I have an array with a list of keywords,
$arr = array('london','england','football',...);
The array could have Nth keywords.
I need to use this on a sql query but i dont want to do too many repetitions like this.
select * from t1 where title LIKE('%$arr[0]%') OR title LIKE('%$arr[1]%') [...]
just need a better solution as each keyword filters out the records.
thnx
One solution would be to create a lookup table that is populated with the rowid and keywordid whenever a row is created or changed.
Then you can skip all the LIKE and get much more performance.
A trigger on insert and update should work but it will cost a little more performance on insert and update.
You would just use
SELECT * FROM t1 WHERE id in (SELECT id FROM lookup WHERE keywordid IN ())
And if the keywords are many you could use a sub query more to get the keyword id's or if fewer use a cached lookup structure directly in code, dictionary or likewise.
LIKE '%nnn%'?I'm unsure whether you wanted to optimize the actual query itself or the means to insert keywords into the query. If it's the latter, then what comes to mind quickly is:
$query = "SELECT * FROM t1 WHERE title";
foreach($arr as $keyword)
{
$stack[] = " LIKE '%$keyword%' ";
}
$query .= implode(' OR title ', $stack);
If you want to avoid full table scans based on keywords, that requires a different topic in itself with more explanation at your end.
Depending on the kind (and amount) of searches you'll be doing, you might need to consider using something else than pure-MySQL for that.
MySQL by itself is not quite well-suited when it comes to fulltext search :
like '%...%' will result in bad performances (scanning all the lines of your table)FULLTEXT index means using MyISAM as storage engine.
If you need to do a lot of searches, it might become more interesting to invest in a solution dedicated to indexing/searching through text, like Solr, or Sphinx.
