Re: MySQL Query Slow Speed
Posted by:
Rick James
Date: August 02, 2010 08:41AM
"WHERE MATCH(title) AGAINST('$name' '*$name' '$name*' '$name *' '* $name' '*$name*' IN BOOLEAN MODE)"
Is $name a "word"? If so, all you need is
WHERE MATCH(title) AGAINST('$name' IN BOOLEAN MODE)
If it is not a word, but part of a word, or an arbitrary string, you are out of luck for performance. And this works better:
WHERE title LIKE '%$name%'
The above match will be fast (but not as fast as by id)
The above LIKE will scan the entire table, tediously checking each title. This is tolerable for a few thousand rows, but not for a million.
WHERE MATCH(title) AGAINST('Air Conditioner' IN BOOLEAN MODE)
Almost does what you want...
ft_min_word_len is probably defaulted to 4, which implies that 'Air' will not be indexed. Still, it will find all `title`s with 'Conditioner', and do it rapidly. To change ft_min_word_len, you have to set it in my.cnf, restart mysqld, and rebuild your index.
But...
There is a difference between these:
WHERE MATCH(title) AGAINST('Air Conditioner' IN BOOLEAN MODE)
WHERE MATCH(title) AGAINST('+Air +Conditioner' IN BOOLEAN MODE)
The '+' says "The field must have the word", which is probably what you want. Without the '+', it will find all the rows, but give a relevance of 0 to the rest. Recommend you add the '+':
PHP:
$str = '+' . preg_replace('/ +/', ' +', $str);
Perl:
$str =~ s{\s+}{ +}g;
$str = "+$str";