Problem with MySQL full text search in combination with OR?

Question

I have a table Document with a full text index *headline_idx* on two columns, headline and text where the text is a MEDIUMTEXT column, there is also some other fields, one of the named lang.

If I do the following select:

select * from Document where lang= 'en' AND match(headLine,text) against ("test")

everything works OK, the fulltext index is used as expected, BUT if I change the AND to an OR like this:

select * from Document where lang= 'en' OR match(headLine,text) against ("test")

the full text index is NOT used, it's not even part of the possible_keys if I do an EXPLAIN EXTENDED which makes any index hints useless.

I would be perfectly happy to add the third column to the full text index, but in the real case that column is in another table. I run version 5.1.35 of MySQL.

Any ideas on what's going on?

Answer 1

Quote from MySql reference: Index Merge is not applicable to fulltext indexes. We plan to extend it to cover these in a future MySQL release.

This particular query can be rephrased with a union and then it will use indexes:

select * from Document where lang= 'en' UNION
select * from Document where match(headLine,text) against ("test")