enigma1 - 2:38 pm on Sep 30, 2010 (gmt 0)
There are certain ways to improve these simple search queries. For instance if we assume the majority of plural words end up in 's' or 'es' then if the first query fails you could initiate a second trimming words ending with these combinations of characters.
You could setup some sensitivity switches so when the query is executed again the chances increase to bring up results removing characters from the end and setting up a threshold to end the operation.
$check_array[$i] = substr($check_array[$i],0,-1);
As each word decreases in length, the chances to retrieve results increase and to compensate for the errors you could utilize functionality perhaps that has to do with pronouncing. In php, there are few like soundex, metaphone, similar_text etc. You could try converting the input words first through the soundex or when you retrieve results that may be inaccurate to filter them through the similar_text. Of course you will need to parse each resulting row again for this and can be intense.
Another thing you may want to do is to precede words in your query with expected separation characters so the results are more accurate.
SELECT * FROM `ads` WHERE `keywords` LIKE 'red%' OR `keywords` LIKE '% red%'......
because you expect 'red' to be at the beginning of a sentence or to be the beginning of a word (eg 'shredding' may also come up when searching for 'red'). For this kind of search you expect the beginning of the phrase to be more accurate than the end.