MySQL Full Text Search Limitations
Suppose you have a MyISAM table containing a column with a full text index. This table starts to grow to a significant size (millions of rows) and gets updated fairly frequently. Chances are that you’ll start to see some bottlenecks when accessing this table, since without row level locking, the reading and writing operations will be blocking each other.
A solution that many people would suggest right away is to use the master for writes and a slave for reads, but this only masks the problem, and it won’t take long before enough read traffic on the slave starts causing slave lags.
The main difference between the Sphinx search engine and other alternatives is its close integration with MySQL. For example, it can be used as a storage engine. In this way, Sphinx’s impact on existing application code …[Read more]