MySQL query with EXTREMELY long Where clause

[seanybob]

Hey all,

Had a question regarding the where clause for mysql.

How long can it be?

Say I wanted to do something like:

SELECT * FROM tablename WHERE id=1 or id=7 or id=19... and on and on for, say, 300 conditions (that are essentially random, and cannot be turned into an algorithm)

So is there a limit? I imagine there has got to be.

Also - can you think of a more efficient way to do this?

[Cronus]

I'm fairly certain there is no limit.

However, the longer it is the longer it will take to execute.

If you have 300 it might take a few extra milliseconds :D

[a_bertrand]

There is a limit, but not due to the size of the condition, it would be something like the limit of the string size of the query, but honestly I never reached it.

Now for your query instead of having id=1 or id=2 etc use

[mysql]SELECT * FROM tablename WHERE id in (1,7,9,10)[/mysql]

It will be faster, shorter to write, and more easy to read. Also if you produce this list from php you can do something like:

 

$search=array();
for($i=0;$i < 10;$i++)
   $search[]=mt_rand(1,1000);
$sql="SELECT * FROM tablename WHERE id in (".implode(",",$search).")";

[seanybob]

Bertrand, once again you pull exactly what I'm looking for out of thin air.

I do have an array of the item in question, so your code is pretty much exactly what I was looking for.

I thank you, kind sir.

*bows*