Full-text search with InnoDB in MySQL

Question

I have a MySQL database (version 5.5.28) with the following table:

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 this in MyISAM. Is it possible to do a similar search in InnoDB without resorting to such things as:

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

For my application (about 1000 - 2000 entries) Sphinx etc. seems redundant. But getting every entry in an array and searching through it using PHP also seems too big. But perhaps you do not agree.

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

Answer 1

Duplicate the text column from products into the new MyISAM table. Establish a 1-1 relationship between them and, to ensure the ACID'ity provided by InnoDB, make sure you always access the MyISAM table with products .

You can add triggers on products to maintain a bijection. You can also create a view so that processing is minimal in your application when you upgrade to MySQL v5.6 (and remove this confusing workaround).

Here is the complete monty .

Instead of copying a text column, you can completely move it (remove it from products , i.e.). It would be more efficient, but it would also be much more difficult to switch to the InnoDB solution only when you want to upgrade.