Jump to content

MySQL query with 100's of WHERE Clauses


jbradley04
Go to solution Solved by Barand,

Recommended Posts

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!

Link to comment
Share on other sites

  • Solution
`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 by Barand
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.