MySQL Forums
Forum List  »  Full-Text Search

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";

Options: ReplyQuote


Subject
Views
Written By
Posted
4286
July 30, 2010 06:05AM
Re: MySQL Query Slow Speed
2180
August 02, 2010 08:41AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.