FULLTEXT search NOT working on two columns

Question

Background:

Hey I'm using

MySQL 5.6.17 InnoDB

, I've read on mysql website that FULLTEXT is now avaiable for InnoDB in 5.6+ version. so i don't have to change from InnoDB to MyISAM Here is the link I altered TWO of my table columns for FULLTEXT search by using the following query

ALTER TABLE `es_officers` ADD FULLTEXT Index_officer_name (es_officer_name)

ALTER TABLE `es_officers` ADD FULLTEXT Index_officer_fname (es_officer_fname)

Altered Table Registered in Information Schema:

Then i checked in my information schema if the altered table columns are registered or not by running the following query

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME

FROM statistics

WHERE index_type LIKE 'FULLTEXT%'

It showed me exacttly two results which i was expecting

Problem:

when i write a query to MATCH a sting AGAINST two columns it gives me an error

SELECT * FROM `es_officers` WHERE MATCH (es_officer_name, es_officer_fname) AGAINST ('abc')



#1191 - Can't find FULLTEXT index matching the column list

BUT:

when i try to query the columns separately like below it works absolutely fine

SELECT * FROM `es_officers` WHERE MATCH (es_officer_name) AGAINST ('abc')



SELECT * FROM `es_officers` WHERE MATCH (es_officer_fname) AGAINST ('abc')

I don't know what is it I'm doing wrong, help is highly appreciated.

Answer 1

Multiples issues:

You have two separate fulltext indexes, each covering a single field. You have WHERE MATCH (es_officer_name, es_officer_fname) as your query, which requires a SINGLE index covering both fields. MySQL will not use two separate indexes for this query - it can't. that's not how fulltext indexing works. You need an alter ... fulltext (es_officer_name, es_officer_fname) instead added.

And even then, abc will fall under the default minimum word length and won't get indexed.

FULLTEXT search NOT working on two columns

Sign up