MySQL fulltext performance solution

Question

How to deal with queries like :

select ... from ... where match(field1) against('someA') and match(field2) against('someB') limit 50

If results returned by condition match(field1) against('someA') & match(field2) against('someB') are both huge,the entire query will be very,very slow.

A solution for this?

Answer 1

Perhaps Sphinx would solve this problem efficiently for you. Sphinx provides powerful fulltext search capability. I'm not sure if that's exactly what you're trying to do, but it sounds close.

Answer 2

You could create another FULLTEXT index for the two columns field1 and field2. Then search for both search terms at once with:

MATCH(field1, field2) AGAINST ('+someA +someB') IN BOOLEAN MODE

Of course, this could find rows where field1 contains both search terms, and field2 contains neither. I'm not sure of the exact requirements of your app.