MySQL InnoDB Text Search Options

Question

Knowing full well that my InnoDB tables don't support FULLTEXT searches, I'm wondering what my alternatives are for searching text in tables ? Is the performance that bad when using LIKE ?

I see a lot of suggestions saying to make a copy of the InnoDB table in question in a MYISAM table, and then run queries against THAT table and match keys between the two and I just don't know that that's a pretty solution.

I'm not opposed to using some 3rd party solution, I'm not a huge fan of that though. I'd like to explore more of what MySQL can do on its own.

Thoughts ?

Answer 1

If you want to do it right you probably should go with Lucene or Sphinx from the very start.

1. it will allow you to keep your table structure.
2. you'll have a huge performance boost (think ahead)
3. you'll get access to a lot of fancy search functions

Both Lucene and Sphinx scale amazingly well (Lucene powers Wikipedia and Digg / Sphinx powers Slashdot)

Answer 2

Using LIKE can only use an index when there is no leading %. It will be a huge performance hit to do LIKE '%foo%' on a large table. If I were you, I'd look into using sphinx. It has the ability to build its index by slurping data out of MySQL using a query that you provide. It's pretty straightforward and was designed to solve your exact problem.

There's also solr which is an http wrapper around lucene, but I find sphinx to be a little more straightforward.

Answer 3

I as others have i would urge use of Lucene, Sphinx or Solr.

However if these are out and your requirements are simple I've used the steps here to build simple search capability on a number projects in the past.

That link is for Symfony/PHP but you can apply the concepts to any language and application structure assuming there is an implementation of a stemming algorithm available. However, if you dont use a data access pattern where you can hook in to update the index when a record is updated its not as easily doable.

Also a couple downsides are that if you want a single index table but need to index multiple tables you either have to emulate referential integrity in your DAL, or add a fk column for each different table you want to index. Im not sure what youre trying to do so that may rule it out entirely.