mysql - Multiple columns in MATCH AGAINST

Question

I'm trying to get results from this query

SELECT ID FROM table1 WHERE MATCH(column1, column2) AGAINST ('text')

But I get this error #1191 - Can't find FULLTEXT index matching the column list

When I put only one column in MATCH it is working, two or more columns within MATCH doesn't work. I've seen people are doing it.

Answer 1

The columns named inside MATCH() must be the same columns defined previously for a FULLTEXT index. That is, the set of columns must be the same in your index as in your call to MATCH().

So to search two columns, you must define a FULLTEXT index on the same two columns, in the same order.

---

The following is okay:

ALTER TABLE tabl1 ADD FULLTEXT INDEX (column1, column2);

SELECT ID FROM table1 WHERE MATCH(column1, column2) AGAINST ('text')

The following is wrong because the MATCH() references two columns but the index is defined for only one column.

ALTER TABLE tabl1 ADD FULLTEXT INDEX (column1);

SELECT ID FROM table1 WHERE MATCH(column1, column2) AGAINST ('text')

The following is wrong because the MATCH() references two columns but the index is defined for three columns.

ALTER TABLE tabl1 ADD FULLTEXT INDEX (column1, column2, column3);

SELECT ID FROM table1 WHERE MATCH(column1, column2) AGAINST ('text')

The following is wrong because the MATCH() references two columns but each index is defined for one column.

ALTER TABLE tabl1 ADD FULLTEXT INDEX (column1);
ALTER TABLE tabl1 ADD FULLTEXT INDEX (column2);

SELECT ID FROM table1 WHERE MATCH(column1, column2) AGAINST ('text')

The following is wrong because the MATCH() references two columns but in the wrong order:

ALTER TABLE tabl1 ADD FULLTEXT INDEX (column1, column2);

SELECT ID FROM table1 WHERE MATCH(column2, column1) AGAINST ('text')

In summary, use of MATCH() must reference exactly the same columns, in the same order, as one fulltext index definition.