2

  1. We use SELECT * FROM profile WHERE id IN (1,2,3...) to get all results in ResultSet. It is essentially SELECT * FROM profile WHERE id='1' OR id='2' OR id='3'...

  2. We write a loop like: foreach(int id: ids) {execute(SELECT * FROM profile WHERE id='i')}

As far as I consider, since requests sent DB takes a lot of time, we should reduce the number of times we visit DB, so the first method is better. However, I am not sure whether it's true or not in the industry and is there's a better solution. Please enlight me, thank you :)

PhoenixPan
  • 536
  • 7
  • 19
  • For security reasons, you should actually use the loop, but in a PDO context. – Strawberry Oct 26 '17 at 04:26
  • 2
    It really depends on your use case. Generally IN() is easier to write and less overhead on the db since it's one question only. @Strawberry, it is indeed safer in a PDO context, but then securitywise it doesn't matter whether it's a loop or not. – marekful Oct 26 '17 at 04:30
  • @marekful well it’s only because of the difficulties of using IN() in prepared statements, but I see that there are some clever hacks – Strawberry Oct 26 '17 at 04:34
  • 1
    @Strawberry Are you conjecturing that the OP is only considering a loop because it does not know how to bind `WHERE IN`? Because if not, your duplicate mark might not be accurate. – Tim Biegeleisen Oct 26 '17 at 04:36
  • Hum...I don't think I am asking the same question as the link points to. Perhaps I didn't state my problem clearly, but the answer by #Tim Biegeleisen and comments by #marekful actually answered my question, thank you! My problem is more about query efficiency and DB optimization. Beyond the question's scope, I am happy to learn more about what I can do to improve it further(PDO), so big thanks to #Strawberry as well! – PhoenixPan Oct 26 '17 at 05:03

1 Answers1

1

From a performance point of view, I think your first query using WHERE IN (...) is the preferred way to go here. As you and the comments have pointed out, calling many queries in a loop from PHP has a big overhead, because each new call to the DB takes network time among other things. On top of this, MySQL can internally optimize a WHERE IN clause. For instance, it can create a map of the IN values which allows for fast lookup. In an ideal case, making a single query using WHERE IN might not peform much worse than searching for a single value.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360