Fulltext search using multiple indexes

Question

I have a table with several columns, say a, b, c, d that should be searchable. The problem arises when I need to search a, b, c separately from d (and vise-versa). AFAIK, there's no way to achieve this using one composite fulltext index on all columns, so I create two separate indexes like this:

CREATE FULLTEXT INDEX idx1 ON content (a, b, c);
CREATE FULLTEXT INDEX idx2 ON content (d);

Now I can search the first and second one successfully. For both of them, I would use the following command:

SELECT * FROM content 
WHERE MATCH(a, b, c) AGAINST ('keyword')
AND MATCH(d) AGAINST ('keyword');

explain tells me this:

+----+-------------+---------+----------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type     | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+----------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | content | fulltext | idx1,idx2     | idx1 | 0       |      |    1 | Using where |
+----+-------------+---------+----------+---------------+------+---------+------+------+-------------+

Great! So it's using two indexes, but returns only rows where keyword is present in both inclusive and I need either one, so I change AND to OR and now explain says:

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | content | ALL  | NULL          | NULL | NULL    | NULL |  128 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

What? Suddenly, it's doing a full table scan. Why is this happening? What would be the best way to avoid this?

Answer 1

Unfortunately, this is how MySQL Query Optimizer treats FULLTEXT indexes. When a MATCH clause is the only clause in the WHERE, the index will be used. When used in conjunction with AND, the index may easily get overlooked.

I wrote about this behavior before in Mysql fulltext search my.cnf optimization

SUGGESTION : Rewrite the query as the union of two FULLTEXT searches

SELECT * FROM content 
WHERE MATCH(a, b, c) AGAINST ('keyword')
UNION
SELECT * FROM content 
WHERE MATCH(d) AGAINST ('keyword');

GIVE IT A TRY !!!

Answer 2

Unclear what you want to do.

If you want to see rows with keyword in both d and somewhere in a,b,c, then your AND is a good way to go.

But if you want keyword to be in any of a,b,c,d, then add a third index

FULLTEXT(a,b,c,d)

and change to

MATCH(a,b,c,d) AGAINST('keyword')

For further discussion please specify whether you are using InnoDB or MyISAM; they work differently.