USE INDEX and MATCH on another FULLTEXT index give the error "Cannot find the FULLTEXT index matching the column list"

Question

Now we are working on vBulletin, which runs on MySQL 5.6.21 with the InnoDB table engine. VBulletin has a default query that uses an index hint in one column and uses a full-text index in two other columns at the same time. The request 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; 

It gives an error

# 1191 - Unable to find FULLTEXT index matching column list

Removing USE INDEX fixes the problem.

This did not happen when implementing the FULLTEXT index in MyISAM, as this is the default query in vBulletin, and it works fine on all boards.

Is it possible that this is some configuration for InnoDB that is causing this problem? We do not control the request itself, so we are looking for a way to solve the problem at the server configuration level.

EDIT: SHOW CREATE TABLE message included

 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 

Answer 1

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

Since you forced the mysql server USE INDEX (threadid) . The server cannot do MATCH for you. Because the server must use your full-text title index to perform a full-text search.

This is why you should remove USE INDEX (threadid) from the query and allow the MySQL server to optimize execution or add a title index, for example USE INDEX (threadid,title)

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

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

And keep in mind that full-text search did not work for InnoDB to scroll through MySQL versions even 5.5; -)