It’s very handy to have FT search out of the box, but there are several drawbacks attached. Problem is that MyISAM Full-text search is not designed to handle big amounts of text data. If you plan to index more than 1M documents you will probably need to take a look on the external search system like Lucene or Sphinx. For the usual LAMP-based service I personally would prefer to use Sphinx as it provides simple transition from MySQL FT and easy to integrate into any application (Sphinx could be queried via native APIs or via MySQL protocol).
Say we have table called <my_table> with `title` and `content` text fields. In MySQL you have to fire query like this:
SELECT * FROM <my_table> WHERE MATCH(`title`,`content`) AGAINST ('I love Sphinx');
Let’s see how could we do the same query with Sphinx.
There are two steps to run the Sphinx as a MySQL FT replacement. First you will need to pull all needed text data from MySQL to Sphinx. For that you have to configure source and index definition in Sphinx search config. Second step is to run indexer program which will connect to MySQL and fetch all the desired data. Then fire up the search daemon which will serve queries. Simplified Sphinx configuration example is below: Continue reading