MySQL 5.6. Full InnoDB Search Text

Question

I understand that MySQL 5.6 is still in beta, but does anyone have experience using the new InnoDB FTS engine? How does it compare with something like Sphinx?

Thanks jason

Answer 1

I never used Sphinx, but tried to use MySQL 5.6 FTS on an Innodb table with about 170 thousand lines. Created an FTS index in the name column (contains all the person’s names). Finding a word anywhere in the line MATCH(name) AGAINST("+word*") IN BOOLEAN MODE works much faster (2-3 times in my case) than using name LIKE "word%" OR name LIKE "% word" . However, when creating joins, check EXPLAIN to see if the FTS is actually used. It seems the MySQL optimizer is not so good at guessing when to use the FTS index.

Answer 2

The FULLTEXT function, which previously required downloading a custom assembly from labs.mysql.com, is now part of the main MySQL assembly in 5.6.5 and later (still in beta). The documentation for FULLTEXT functions now includes InnoDB details: MySQL full-text search functions

Answer 3

Remember that Sphinx search is designed for full-text search in mysql, it's just a function ...

Here you compare sphinx and mysql FTS: http://www.percona.com/files//presentations/opensql2008_sphinx.pdf

Here is the InnoDB FTS performance benchmark compared to MyISAM: http://blogs.innodb.com/wp/2011/07/innodb-fts-performance/

InnoDB its bit is faster, especially in indexing, but it is still far from sphinx performance ...