In an effort to help a colleague with
FULLTEXT search troubles today, I tried to find out everything that could go wrong with setting up this search method on a table.
My short research resulted in this checklist. Failure to comply with these checks will result in catastrophic failure :P
Make sure the table you’re trying to
FULLTEXTsearches do NOT work on InnoDB types. If you are bound to using
InnoDBfor various reasons, you could create a duplicate table in
MyISAMto perform your searches on.
Ensure that your tables have
FULLTEXTtype for all columns you’re trying to perform
FULLTEXTsearches on. If you don’t have such an index, you can easily add it by using:
ALTER TABLE xxx ADD FULLTEXT(yyy,zzz);
xxxis your table name, and
zzzare the columns you want to search. Do this for earch individual column or set of columns you want to search.
If you changed anything in your setup, you might want to rebuild the Indexes. This is done by issuing:
REPAIR TABLE xxx;
If you are building a search function for a product catalog, it’s likely that you have to match against short words. e.g. a product that has the name
FULLTEXTdefaults to using 4 characters for the shortest possible word, so your
16Cis not a valid word, and will be omitted in search results. You can change this by editing your
my.cnf(likely to reside in
/etc/mysql/my.cnfon Linux based systems), and adding the
ft_min_word_lendirective to the appropriate sections:
[mysqld] ft_min_word_len = 2 [myisamchk] ft_min_word_len = 2
Do note that shortening the minimum word length significantly increases the load on your MySQL server, so this might be a VERY bad idea on large tables/busy systems. (You might as well go back to using
%LIKE%searches ;)). After you added the directives, restart MySQL (e.g.
/etc/init.d/mysql restart), and rebuild your table indexes using the
REPAIRcommand from check 3.
BLOBis a commonly used fieldtype for fields that contain large bits of content.
BLOBis a binary type though, and it disables the possibility to use
FULLTEXTsearches on fields that have the
BLOBtype. Solution is to convert the
BLOBfields you want to search to the non-binary
TEXTtype. (Or any equivalent type of your choise). This can be done by issuing:
ALTER TABLE xxx MODIFY yyy TEXT;
xxxis the tablename and
Last but not least.. make sure you have a MySQL version running that supports
FULLTEXThas been around since 3.23.23. If you want to use the more advanced
IN BOOLEAN MODEdirective in your
AGAINST(), you need to have a whopping MySQL 4.1 or better available to you. (Just install MySQL5 and you’re in the green!;)) If you are GO on all these checks, and it still doesn’t work.. it might very well be your Query that’s messed up.
Although the FULLTEXT basics are out of this blogposts scope , here’s a very basic
FULLTEXTquery that should work:
SELECT * FROM xxx WHERE MATCH(yyy,zzz) AGAINST ('+aaa -bbb' IN BOOLEAN MODE);
zzzare columns (make sure they’re indexed in a group!),
aaawill be included,