Wcan Wcan - 1 year ago 84
MySQL Question

FULLTEXT search NOT working on two columns


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

FROM statistics

It showed me exacttly two results which i was expecting

Information Schema results


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


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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download