seanybob Posted November 24, 2009 Share Posted November 24, 2009 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? Quote Link to comment Share on other sites More sharing options...
Cronus Posted November 24, 2009 Share Posted November 24, 2009 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 Quote Link to comment Share on other sites More sharing options...
a_bertrand Posted November 25, 2009 Share Posted November 25, 2009 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).")"; Quote Link to comment Share on other sites More sharing options...
seanybob Posted November 25, 2009 Author Share Posted November 25, 2009 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* Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.