CODEWITHSUNDEEP
mysqlfull-text-searchinnodbsphinx
Jason
Jason

Reputation: 683

MySQL 5.6 InnoDB Full Text Search

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

Thanks Jason

Upvotes: 7

Views: 4536

Answers (3)

Max Webster
Max Webster

Reputation: 334

The FULLTEXT feature that formerly required downloading a special build from labs.mysql.com is now part of the mainline MySQL build in 5.6.5 and up (still in beta). The documentation for the FULLTEXT functions now includes the InnoDB-specific details: MySQL Full-Text Search Functions

Upvotes: 2

Artem Goutsoul
Artem Goutsoul

Reputation: 753

Never used Sphinx, but tried MySQL 5.6 FTS on an Innodb table with about 170k rows. Made an FTS index on the name column (contains all names of a person). To find a word in any position of the string MATCH(name) AGAINST("+word*") IN BOOLEAN MODE does work a lot faster (2-3 times in my case) than using name LIKE "word%" OR name LIKE "% word". However when making joins do check EXPLAIN to see if the FTS index is actually used. It seems MySQL optimizer is not that good at guessing when the FTS index should be used.

Upvotes: 3

rogal111
rogal111

Reputation: 5933

Remember, that Sphinx search is developed for full text searching in mysql it's just a feature...

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

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

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

Upvotes: 1

Related Questions