Best practices(or best engine) for search performance AND data integrity [closed]

Question

This might be too general or subjective of a question but I need help and I'm not even sure what the correct and concise questions are. I've done a lot of google searches in the past few days trying to make sense of this and I'm more confused than ever about what approach to take.

In MySQL I've built a database for a product catalog. I have to keep track of a few many-to-many relationships such as products-tags, products-categories, so I decided I would need to use INNODB in those tables, so that I could make use of FOREIGN KEY constraints. That's all fine, BUT. INNODB does not support FULLTEXT. I've read that it is bad performance to use LIKE '%WORD%' in WHERE clause because no index can be used when wildcard prepends a search word. The database will likely have a lot of entries at some point and I don't want searches to take forever.

So how does one have their cake and eat it too? Do I need to choose one or the other: FULLTEXT or FOREIGN KEY? Should I make a sort of summary MYISAM table consisting of only the most search relevant columns, and use triggers on the INNODB tables to keep it updated? Wouldn't that -at least partially- defeat the bonus of using FOREIGN KEY constraints in the first place: eliminating duplicate and superfluous data? What is the best pattern to make searching a large database work with the best possible performance - at least in respect to using INNODB vs. MYISAM?

I would greatly appreciate it if someone bright and experienced could at least point me in the right direction. Thanks in advance.

Answer 1

I did a webinar presentation last year Full Text Search Throwdown to compare different full-text search tools, including the MyISAM FULLTEXT index, the new implementation of InnoDB FULLTEXT index in MySQL 5.6. You can also find my slides on Slideshare: Full Text Search Throwdown.

For performance, Sphinx Search was the clear winner. Most sites I have consulted for use InnoDB to store their canonical data, and use Sphinx Search to store an auxiliary index of that same data. Then you get the best of both worlds: data integrity with InnoDB, and speed with Sphinx Search.

The downside of Sphinx Search is that it's hard to add more content to a given Sphinx Search index incrementally. Adding a single row to an index takes about as much work as reindexing the whole collection. There are a couple of workarounds for this.

InnoDB FTS is pretty new, and it has some odd quirks. You might like to read an in-depth review by my colleague at Percona:

• InnoDB Full-text Search in MySQL 5.6: Part 1
• InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

PS: Don't use MyISAM. :-)