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.
Thanks
Suraj