one big query or many small queries?

using php array loop or mysql IN clause

[bjmosk]

Let's say i have a list of 1,000 photo IDs that I need to look up in my mysql database. Given this large number, which is preferred?

1. Loop through all 1,000 IDs and run a SELECT query for each
2. Build one massive query and put all 1,000 photo IDs in the SELECT query's IN clause

I know in general it's better to run less queries, but i'm just not sure if the IN clause is meant to contain so many values (and perform reliably).

Thanks in advance,
Brian

[CWebguy]

I'm thinking maybe there is a better way to write your code?

[bjmosk]

This is an example that is obviously intended to present an extreme case. I am just asking, from a theoretical standpoint, about the comparative stress each would put on my database. Thanks though.

[enforcer2k]

I would guess #2 would be the better choice for the DB since it will not have to deal with 1000 open/processed/ and closed connections and may be better able to employ caching over time to minimize internal calculations. However user experience may suffer because the user will have to wait for the entire result set to be returned from then DB before seeing anything.

#1 would suit a more async Ajax situation in which the data is displayed as results from the DB are returned over time...

Anyone have any other ideas? I hope the photos are not being stored in the DB too... that would be a loong wait. Better to store a filesystem reference I think.

[Demaestro]

in your example... are the 1000 ids more or less the same list from user to user?

user A.. hits this function and uses the 1000 ids

user b hits this function, are they using the same ids?

Even if the 1000 becomes a different 1000 on another day is it the same 1000 or so that each user will be checking against?

[Demaestro]

Anyway I ask because if ^^this^^ is so then using views or query caching could help speed things up if you do it all in SQL.

Another thing to look at is setting it up as a stored procedure.

[LifeinAsia]

I agree with CWebguy. Does each page need to use all 1000 IDs?

Fewer calls to the DB is usually preferable, so option #2 would probably be better. We've done INs with tens of thousands of entries (although it's mostly for back-end processing/maintenance, not front-end processing).