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

Answer 1

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 ;-)