USE INDEX and MATCH on another FULLTEXT index gives error "Can't find FULLTEXT index matching the column list"

Question

Working now on a vBulletin board, which runs on MySQL 5.6.21 with InnoDB table engine. There is a default query in vBulletin, which uses index hint on one column and the same time uses fulltext index on another two columns. The query looks like

SELECT postid, post.dateline FROM post AS post 
USE INDEX (threadid) 
INNER JOIN thread AS thread ON(thread.threadid = post.threadid) 
WHERE MATCH(post.title, post.pagetext) AGAINST ('+atlantic +blue +tang' IN BOOLEAN MODE) 
AND thread.threadid = 170467;

This gives error

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

Removing USE INDEX resolves the problem.

This was not happening on MyISAM implementation of FULLTEXT index for sure, as this is the default query on vBulletin and it runs fine on all boards.

Is it possible this is some configuration for InnoDB, that causes this issue? We don't have control over the query itself, so looking for a way to resolve the issue on server configuration level.

EDIT: Included SHOW CREATE TABLE post

CREATE TABLE `post` (
 `postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `threadid` int(10) unsigned NOT NULL DEFAULT '0',
 `parentid` int(10) unsigned NOT NULL DEFAULT '0',
 `username` varchar(100) NOT NULL DEFAULT '',
 `userid` int(10) unsigned NOT NULL DEFAULT '0',
 `title` varchar(250) NOT NULL DEFAULT '',
 `dateline` int(10) unsigned NOT NULL DEFAULT '0',
 `lastedit` int(10) unsigned NOT NULL DEFAULT '0',
 `pagetext` longtext NOT NULL,
 `allowsmilie` smallint(6) NOT NULL DEFAULT '0',
 `showsignature` smallint(6) NOT NULL DEFAULT '0',
 `ipaddress` varchar(15) NOT NULL DEFAULT '',
 `iconid` smallint(5) unsigned NOT NULL DEFAULT '0',
 `visible` smallint(6) NOT NULL DEFAULT '0',
 `attach` smallint(5) unsigned NOT NULL DEFAULT '0',
 `infraction` smallint(5) unsigned NOT NULL DEFAULT '0',
 `reportthreadid` int(10) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`postid`),
 KEY `userid` (`userid`),
 KEY `threadid` (`threadid`,`userid`),
 KEY `dateline` (`dateline`),
 FULLTEXT KEY `title` (`title`,`pagetext`)
) ENGINE=InnoDB AUTO_INCREMENT=1634030 DEFAULT CHARSET=utf8

You do not need to use `use index` for full text search. Mysql knows that your `post.title, post.pagetext` are setup with full text index and more over if the column `thread.threadid` is index, optimizer will do its job. — Abhik Chakraborty, Apr 17 '15 at 07:15
@AbhikChakraborty As mentioned, this is default query from vBulletin, and I can't change it. — Harutyun Drnoyan, Apr 17 '15 at 07:27
Ah, another reason to shun 3rd party packages. Complain to vBulletin. Meanwhile, it _might_ work to do `ALTER TABLE post ENGINE=MyISAM;` — Rick James, Apr 17 '15 at 19:43
@RickJames Customer uses InnoDB for performance reasons, and changing one table back to MyISAM is not possible as it will break all JOINs between tables in different engines. — Harutyun Drnoyan, Apr 19 '15 at 20:06
JOINs between different engines works. Transactions and FOREIGN KEYs don't work with MyISAM -- mostly ignored. — Rick James, Apr 19 '15 at 20:29
@AbhikChakraborty apparently the optimized does NOT do its job: removing the USE INDEX clause does make the query work (though it's not a solution to the OP), but EXPLAIN shows that MySQL still prioritizes the fulltext index resulting in horrible inefficiency. — matteo, Aug 31 '15 at 15:37
@Alex Here is the scheme. I solved the problem by programmatically removing the index from the query, but the main question of why I get the error remains valid. — Harutyun Drnoyan, Sep 11 '15 at 14:23

Alex · Answer 1 · 2015-09-11T15:03:55.460

http://sqlfiddle.com/#!9/21fa5/3

Since you forced mysql server to USE INDEX (threadid). Server can't do MATCH for you. Because to execute fulltext search server must use your fulltext index title.

That is why you should remove USE INDEX (threadid) from query and allow MySQL server to optimize the execution itself, or add title index like USE INDEX (threadid,title)

UPDATE I agree with you, that is weird : http://sqlfiddle.com/#!9/e363e/1

If table type is MyISAM query works even if you USE INDEX (threadid) but it seems to me like some feature of using MyISAM. When I use USE INDEX I have to be very sure what am I doing. So even if it works for MyISAM I would prefer to set USE INDEX (threadid,title) if that is the way I prefer.

And keep in mind that fulltext search didn't work for InnoDB for prvious versions of MySQL even 5.5 ;-)

USE INDEX and MATCH on another FULLTEXT index gives error "Can't find FULLTEXT index matching the column list"

1 Answers1