CODEWITHSUNDEEP
mysqlsqlselect
rolinger
rolinger

Reputation: 3058

mysql efficiency of using 'WHERE id IN (list of IDs)'

I have written some scripts that are grabbing all records that match a list of IDs. It works just fine:

select * from invoices where iOpen=1; // collect open invoices

Companies can have 1 or 20+ open invoices at any one time. Using php, looping through the results to collect the companyID once, then use the companyID list to grab other corporate data:

select ct.*,bs.* from clientTable ct, billSettings bs where ct.cID=bs.cID AND ct.cID IN (companyIDs);

When the LIST (companyIDs) is 5 to 10 items long its no problem....but what happens when that list grows to 100 IDs or 500 IDs or more? Technically, its still just one call against the DB versus iterating a loop that would hit the DB 100 or 500 times. But at what point does the single call itself become inefficient?

Is there another method that could get the same data that is more efficient?

Upvotes: 0

Views: 391

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Actually, MySQL is very good about optimizing this. It actually sorts the values and does a binary search on the values.

This is not a common optimization in other databases.

Upvotes: 1

Related Questions