MySQL fulltext index search with multiple columns

Question

MySQL Table - sample_table: col1 col2 col3 col4

Fulltext index: Keyname: some_keyname Columns: col1, col2, col3, col4

Question:

To search for a string "abcd" in any column, do we have to match all column names? Like this:

SELECT col2, col3 from sample_table
where MATCH(col1, col2, col3, col4) AGAINST ("abcd")

Or it is possible to use the keyname (or something similar)? Such as:

SELECT col2, col3 from sample_table
where MATCH(some_keyname) AGAINST ("abcd")

Thanks in advance for all responses!

(The following works, of course..)

SELECT col2, col3 from sample_table
where MATCH(col1, col2, col3, col4) AGAINST ("abcd")

Bill Karwin · Accepted Answer · 2025-06-24 18:27:48Z

2

The columns you name in MATCH() must be all the columns defined for the fulltext index.

Example:


create table mytable (
  id serial,
  t1 text,
  t2 text,
  t3 text,
  fulltext index (t1, t2, t3)
);

insert into mytable values (null, 'one row', 'with', 'a word');
insert into mytable values (null, 'one row', 'without', 'what we look for');

select * from mytable where match(t1, t2, t3) against('word');
+----+---------+------+--------+
| id | t1      | t2   | t3     |
+----+---------+------+--------+
|  1 | one row | with | a word |
+----+---------+------+--------+

select * from mytable where match(t1) against('word');

ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

The last query doesn't work because no fulltext index is defined for the single column t1.

edited Jun 24 at 18:27

answered Jun 24 at 18:21

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Akina Jun 24 at 18:32

The columns you name in MATCH() must be all the columns defined for the fulltext index ... in any order.

Bill Karwin Jun 24 at 18:34

Correct. I edited my answer after I tested it. I thought the order of columns needed to match the order in the index definition. Perhaps I'm remembering some earlier version of MySQL, or maybe I'm just mistaken.

Akina Jun 24 at 18:58

dbfiddle.uk/dEghF5Fp

Rick James Aug 17 at 4:16

(MyISAM had different rules on this topic.)

Bill Karwin Aug 17 at 5:16

I've been assuming InnoDB for MySQL answers for about ten years. Virtually no one should be using MyISAM these days.

Collectives™ on Stack Overflow

MySQL fulltext index search with multiple columns

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related