MySQL Bugs: #84665: fulltext index with boolean mode not working on derived table

Bug #84665	fulltext index with boolean mode not working on derived table
Submitted:	25 Jan 2017 13:57	Modified:	27 Feb 2017 4:07
Reporter:	Suraj Sawant	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S5 (Performance)
Version:	5.7.15	OS:	CentOS
Assigned to:		CPU Architecture:	Any
Tags:	derived table optmization, full text index

Description:
Hi All,

We have recently upgraded our Mysql Dev servers from 5.6.27 to 5.7.15.
After upgrade we are facing some issues with full text index.

Query in our case is:

SELECT * FROM (SELECT * FROM TABLENAME WHERE COLUMN2=KEYWORD2)FOO WHERE MATCH(COLUMN1) AGAINST(KEYWORD IN BOOLEAN MODE)

P.N We are filtering rows by using subquery as table has more than 45 million rows.

Before upgrade query used to run within seconds(1-2 seconds max).But after upgrade query is taking minutes.

After checking the plan,we found that query is being optmized into following format as there are some optmization with derived tables in 5.7.15.

SELECT * FROM TABLENAME WHERE COLUMN2=KEYWORD2 AND MATCH(COLUMN1) AGAINST(KEYWORD IN BOOLEAN MODE).

So we disabled the derived table marge by changing the optimizer_switch variables with derived_merge=off.

but then it started giving error-Can't find FULLTEXT index matching the column list

On further investigation,we found that in mysql 5.7.6 onwards internal temp tables engine is changed from myisam to innodb.
so we changed internal_tmp_disk_storage_engine to myisam(full text index run in boolean mode without index creation)

then it started giving error-can not create full text index on materialized subquery.

We tried disabling materialization from optmizer switch variable.but id didn't help.
Need help to resolve this.

How to repeat:
1.Change following:
optmizer switch=index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=on,materialization=off,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=off,use_index_extensions=on,condition_fanout_filter=on,derived_merge=off

internal_tmp_disk_storage_engine=myisam

Sql Query format:
SELECT * FROM (SELECT * FROM TABLENAME WHERE COLUMN2=KEYWORD2)FOO WHERE MATCH(COLUMN1) AGAINST(KEYWORD IN BOOLEAN MODE)

P.N. TABLENAME HAS FULL TEXT INDEX CREATED ON COLUMN COLUMN1.

Suggested fix:
derived table with internal_tmp_disk_storage_engine=myisam storage engine should not create full text index.

Hi Suraj,

Thank you for the report.
In order to reproduce the issue at our end, could you please provide exact repeatable test case(CREATE TABLE and subset of data to reproduce)? If the data you need to attach is more than 3MB, you should create a compressed archive of the data and a README file that describes the data with a filename that includes the bug number (recommended filename: mysql-bug-data-84665.zip) and upload one to sftp.oracle.com

Thanks,
Umesh

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".