Wcan Wcan - 5 months ago 18
MySQL Question

FULLTEXT search NOT working on two columns

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

Information Schema results

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

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.