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

Selfgovernment

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. – Molybdate

@AbhikChakraborty As mentioned, this is default query from vBulletin, and I can't change it. – Selfgovernment

Ah, another reason to shun 3rd party packages. Complain to vBulletin. Meanwhile, it might work to do ALTER TABLE post ENGINE=MyISAM; – Argilliferous

@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. – Selfgovernment

JOINs between different engines works. Transactions and FOREIGN KEYs don't work with MyISAM -- mostly ignored. – Argilliferous

@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. – Ibby

post your post table schema please – Colyer

@Colyer 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. – Selfgovernment

what is your mysql server version? – Colyer

@Colyer it is 5.6.21-log – Selfgovernment

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

Colyer