Fulltext search with InnoDB in MySQL

Question

I have a MySQL database (version 5.5.28) with a table like this:

products (InnoDB)
-------------------------------------------------
search_id     title               description
1             Levi Blue Jeans     Some cool jeans 
2             Gucci Handbag       Great accessory
3             Prada Dress         Beautiful dress

I want to do something like this in MySQL:

SELECT MATCH(title) AGAINST ('Jeans') AS score, search_id, FROM search WHERE MATCH(title) AGAINST ('Jeans' IN BOOLEAN MODE)

As far as I know you can only do that in MyISAM. Is it possible to do a similar search with InnoDB without resorting to things like:

• Sphinx
• Elasticsearch
• Getting MySQL 5.6.4 which seems to not be stable yet

For my application (around 1000 - 2000 records) Sphinx etc. seems like overkill. But getting every record into an array and search through it with PHP also seems too much. But maybe you disagree.

PS. LIKE doesn't seem to work well for me. The results are usually less accurate and you cannot get a score as you can with MATCH.

Hearst

Looks like MySQL 6 will support some fulltext features, but you may have to use something else in the meantime. – Reconnaissance

Answer 1

Duplicate the text column from products to a new MyISAM table. Establish a 1-1 relationship beween the two, and, in order to ensure of the ACID'ity provided by InnoDB, make sure that you always access the MyISAM table together with products.

You may want to add triggers on products to maintain the bijection. You could also create a view so that the rework is minimal in your application when you upgrade to MySQL v5.6 (and drop this convoluted workaround).

Here is the full monty.

Instead of copying the text column, you could move it altogether (delete it from products, that is). This would be more efficient, but it would also make it a bit more complicated to switch to a InnoDB-only solution when you feel like upgrading.

Keitloa

super easy solution for a tricky question. works like a charm. :) – Cookbook