mysql innodb 使用中,在MySQL中使用InnoDB进行全文搜索

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:

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.

解决方案

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

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.