Background:
Hey I'm using
MySQL 5.6.17 InnoDB
ALTER TABLE `es_officers` ADD FULLTEXT Index_officer_name (es_officer_name)
ALTER TABLE `es_officers` ADD FULLTEXT Index_officer_fname (es_officer_fname)
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM statistics
WHERE index_type LIKE 'FULLTEXT%'
SELECT * FROM `es_officers` WHERE MATCH (es_officer_name, es_officer_fname) AGAINST ('abc')
#1191 - Can't find FULLTEXT index matching the column list
SELECT * FROM `es_officers` WHERE MATCH (es_officer_name) AGAINST ('abc')
SELECT * FROM `es_officers` WHERE MATCH (es_officer_fname) AGAINST ('abc')
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.