{"id":791,"date":"2008-04-15T14:36:52","date_gmt":"2008-04-15T19:36:52","guid":{"rendered":"http:\/\/www.bytebot.net\/blog\/archives\/2008\/04\/15\/mysql-full-text-search-by-alex-rubin"},"modified":"2008-04-15T14:38:59","modified_gmt":"2008-04-15T19:38:59","slug":"mysql-full-text-search-by-alex-rubin","status":"publish","type":"post","link":"http:\/\/www.bytebot.net\/blog\/archives\/2008\/04\/15\/mysql-full-text-search-by-alex-rubin","title":{"rendered":"MySQL Full Text Search by Alex Rubin"},"content":{"rendered":"

Download the PDF: http:\/\/www.mysqlfulltextsearch.com\/full_text.pdf<\/a><\/p>\n

Default search by relevance, default sort is by relevance<\/p>\n

Boolean search is also popular. cats AND dogs. No default sorting, so you need to order the results yourself<\/p>\n

Phrase search<\/p>\n

MySQL Full Text Index, only available with MyISAM, and it supports natural language and Boolean search. ft_min_word_len – 4 characters per word by default is indexed. Frequency based ranking, doesn’t count distance between words<\/p>\n

SELECT * FROM articles WHERE MATCH (title,body) AGAINST (‘database’ IN NATURAL LANGUAGE MODE);<\/p>\n

For Boolean, you use AGAINST (‘cat AND dog’ IN BOOLEAN MODE).<\/p>\n

n-gram fulltext plugin for CJK languages are available as plugins<\/p>\n

DRBD and MySQL FullText search? DRBD requires InnoDB, when there is a failover, DRBD needs to perform a reovery. Fulltext only works for MyISAM. So ou create a “FullText” slave MyISAM table with FullText indexes. The slide (diagram) is most useful for this, naturally.<\/p>\n

Speed up FT search? Fit the index into memory. key_buffer = total size of full text index (max=4GB). You can preload FT indexes into buffer.<\/p>\n

You can manually partition. Partitioning decreases index and table size, so search is faster. Application needs changing of course. MySQL 5.1 partitioning features, do not support FTs.<\/p>\n

Order by\/Group by is a performance killer. Using order by date, is much slower than with no order by.<\/p>\n

Real World Performance Killer<\/b>
SELECT … FROM `ft` WHERE MATCH `album` AGAINST (‘the way i am’)
The above query, is very slow! It took like 13 seconds or so.<\/p>\n

Note the stopword list and ft_min_word_len. I is not a stopword, but “the”, “way”, and “am” are stopwords.<\/p>\n

ft_min_word_len = 1 will mean that all words except “i” will be filtered out with the standard stoplist. “i” is contained in lots of text!<\/p>\n

Search with error correction? Use soundex() MySQL function (sounds similar). select soundex(“Dilane”) should equate to Dylan. You can sort it either by popularity or Levenstein distance (either by a stored procedure or a UDF).<\/p>\n

Sphinx – nice, open source, can be faster than MySQL full text index on a large dataset, supports multi-node clustering out of the box. It is however an external solution that isn’t built-in, and needs to be integrated.<\/p>\n

MySQL 5.0: need to patch source code. MySQL 5.1: copy Sphinx plugin to the plugin_dir.<\/p>\n

You can set Sphinx to be MySQL’s storage engine if you like.<\/p>\n

Resources<\/b><\/p>\n