jbradley04 Posted June 14, 2013 Share Posted June 14, 2013 I am doing a MySQL query in which the WERE clause contains an array that would have up to 100's of clauses. Basically I have a DB of words. When people enter letters to search for words containing those letters, and only those letters, it will at that to the query. For Example: * The code below is extremely abbreviated but you should get the point. <?php /* Say someone enters "abc" So I have it setup that $word = 'a' , OR `word` = 'b' , OR `word` = 'c' , OR `word` = 'ab' , OR `word` = 'ac' , OR `word` = 'ba' , OR `word` = 'ca' , OR `word` = 'cb' , OR `word` = 'abc' , OR `word` = 'cba' , OR `word` = 'bca' */ $sql = mysql_query("SELECT * FROM `words` WHERE `word` = ".$words." ORDER BY `word` DESC"); ?> Well this turns out to be quick with "abc" but if you enter abcdefg the combinations would be thousands and brutally slow! Is there a way to speed things up with a better, more optimized query? Thanks for your help! Quote Link to comment https://forums.phpfreaks.com/topic/279189-mysql-query-with-100s-of-where-clauses/ Share on other sites More sharing options...
mac_gyver Posted June 14, 2013 Share Posted June 14, 2013 see the posts near the end of this thread - http://forums.phpfreaks.com/topic/279088-question-about-a-query-i-thought-would-be-a-lot-simpler/ Quote Link to comment https://forums.phpfreaks.com/topic/279189-mysql-query-with-100s-of-where-clauses/#findComment-1436063 Share on other sites More sharing options...
jbradley04 Posted June 14, 2013 Author Share Posted June 14, 2013 Well, that it is definitely the most progress I have made for speed! The problem is when I enter abcdhlkd blackball comes as a result. This cannot be though because there is only one l Any thoughts? Quote Link to comment https://forums.phpfreaks.com/topic/279189-mysql-query-with-100s-of-where-clauses/#findComment-1436069 Share on other sites More sharing options...
Solution Barand Posted June 14, 2013 Solution Share Posted June 14, 2013 (edited) `word` = 'a' OR `word` = 'b' OR `word` = 'c' OR `word` = 'ab' OR `word` = 'ac' OR `word` = 'ba' OR `word` = 'ca' OR `word` = 'cb' OR `word` = 'abc' OR `word` = 'cba' OR `word` = 'bca' */ can be simplified to `word` IN ('a', 'b', 'c', 'ab', 'ac', 'ba', 'ca', 'cb', 'abc', 'cba', 'bca') Edited June 14, 2013 by Barand Quote Link to comment https://forums.phpfreaks.com/topic/279189-mysql-query-with-100s-of-where-clauses/#findComment-1436071 Share on other sites More sharing options...
jbradley04 Posted June 14, 2013 Author Share Posted June 14, 2013 Ok, Great! That works awesome! I really appreciate all the help! Quote Link to comment https://forums.phpfreaks.com/topic/279189-mysql-query-with-100s-of-where-clauses/#findComment-1436073 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.