MySQL Forums
Forum List  »  Full-Text Search

Slow full text index search in Mysql 5.7
Posted by: Suraj Sawant
Date: January 24, 2017 02:05AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow full text index search in Mysql 5.7
2673
January 24, 2017 02:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.