7

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

Jason
  • 683
  • 2
  • 9
  • 14
  • 1
    Have a look around: http://blogs.innodb.com/wp/2011/07/difference-between-innodb-fts-and-myisam-fts/ http://blogs.innodb.com/wp/2011/07/innodb-fts-performance/ http://blogs.innodb.com/wp/2011/07/innodb-full-text-search-tutorial/ http://blogs.innodb.com/wp/2011/07/overview-and-getting-started-with-innodb-fts/ – Jauzsika Nov 19 '11 at 12:58
  • Is there a specific feature or performance metric you are looking for? As stated, your question is not likely to be constructive and gain a non-subjective answer. – Kris Nov 25 '11 at 12:21

3 Answers3

3

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.

Artem Goutsoul
  • 733
  • 5
  • 17
2

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

Max Webster
  • 181
  • 6
1

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...

rogal111
  • 5,874
  • 2
  • 27
  • 33
  • 2
    Still, it's much easier to use one product instead of two. Especialy, when you implemend all this to customer and go away. In our big projects we use sphinx, of course. – Oroboros102 Nov 26 '11 at 08:39