4

I have this slow query (26 seconds):

SELECT uniqueIDS FROM search_V2 WHERE siteID=1 AND status=1 AND (MATCH(data) AGAINST ('scale' IN BOOLEAN MODE));

I like first the query work on siteID_status index and then work with the full search index.

Any solution for that?

Noamway
  • 155
  • 5
  • 21
  • Does your query use all indexes? This would be `INDEX siteID_status (siteID, status)` and `FULLTEXT indexname (data)`. Check out with EXPLAIN. – chriopp May 11 '18 at 08:03
  • @Christoph no, only this one FULLTEXT index. It will be great if it will first use **siteID_status** and then the FULLTEXT index. I don't know how to do that. – Noamway May 12 '18 at 20:49
  • @Noamway If the search queries are used multiple times, work with optimized views, possibly the query could also be optimized with a temporary table. – Philipp Nies May 16 '18 at 09:21

2 Answers2

2

So this is the best solution I found:

SELECT uniqueIDS FROM
(
    SELECT * FROM search_V2 WHERE siteID=1 AND status=1
) A
WHERE MATCH(data) AGAINST ('scale' IN BOOLEAN MODE);

This post really help me: https://dba.stackexchange.com/questions/15214/why-is-like-more-than-4x-faster-than-match-against-on-a-fulltext-index-in-mysq/15218#15218?newreg=36837d8616984e289377475b27ee0758

I hope it will help someone else also.

Noamway
  • 155
  • 5
  • 21
0

You can tell the query which index to follow first. See Index hints at https://dev.mysql.com/doc/refman/8.0/en/index-hints.html. Without testing it your query would be something like

SELECT uniqueIDS FROM search_V2 WHERE siteID=1 AND status=1 AND (MATCH(data) AGAINST ('scale' IN BOOLEAN MODE)) USE INDEX(siteID_status,fulltext_index);

with fulltext_index being the name of your fulltext index. Hope it works, but you are close.

chriopp
  • 947
  • 7
  • 12
  • 1
    That didn't help me. He still use only one index. Maybe because one of the index is full search. Anyway I found better solution to my problem – Noamway May 13 '18 at 01:19