Problems using MySQL FULLTEXT search

Question

I have already posted a question about this, but the situation has changed sufficiently to warrant a new one.

I have a MySQL table called aromaProducts in which there are 7 columns with the FULLTEXT index, and which has three records in it. When I make a query against it like:

SELECT * FROM aromaProducts WHERE MATCH (title) AGAINST ('chamomile');

I get the correct result. However, when I try adding a second field to search in, I get an error:

Can't find FULLTEXT index matching the column list

Every column on its own works fine. I have also explicitly adding WITH QUERY EXPANSION and the same thing.

I have another table, aromaProducts1, and instead of assigning FULLTEXT to the fields one at a time, I assigned it to all 7 at the moment of table creation. Against this table, no queries work. When examining the table structure, the difference is this:

The first table shows each field having its own FULLTEXT index, while the second has one index, named title (the first field to have it assigned), and it applies to all seven fields.

All columns that I have made FULLTEXT are either VARCHAR or TEXT datatypes. I have no clue what the problem is.

Answer 1

You have to create the fulltext index on all columns you are going to match

If you want to match on (col1,col2,col3) you have to create fulltext index on col1,col2 and col3. If you want to match on(col1,col2) you have to implement another fulltext index(on col1 and col2), you can't use that one on col1, col2 and col3

Answer 2

you have to create one fulltext index with several columns, not one for each