CODEWITHSUNDEEP
mysqlsql
PhoenixPan
PhoenixPan

Reputation: 551

WHERE IN (a,b,c...) or WHERE= in loop, which one usually performs better?

  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 :)

Upvotes: 2

Views: 313

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

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.

Upvotes: 1

Related Questions