DB: MySQL 8.0, InnoDB engine; Table size: About 2M rows of 2G data; FULL_TEXT index column: sentence (TEXT data type) https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
- Old Query using SQL LIKE:
SELECT * FROM books WHERE sentence LIKE '%This is a sample search input string%' and author_id = 5 and publisher_id = 23;
- New Query using MySQL FULL_TEXT search:
SELECT * FROM books WHERE MATCH (sentence) AGAINST ('This is a sample search input string') and author_id = 5 and publisher_id = 23 LIMIT 1;
Problems: I expect a lot of search speed boost from using LIKE to FULL_TEXT(match... against). But based on my testing, this isn't the case: For an input string with <10 words, full_text search is faster than LIKE; For an input string with ~25 words, the full_text search can take 3+ seconds to return which is similar to LIKE. And longer the string, the worse speed full_text search has which can take more than 15s.
Profiling the query: https://dev.mysql.com/doc/refman/8.0/en/show-profile.html By looking at the profiling result, 90% of the time is spent on "FULLTEXT initialization"
Optimization I've tried which haven't brought speed improvement:
6.1 Rewrite query trying to use other indexes together with full-text index:
select * from books as b1 join books b2 on b1.author_id = b2.author_id and b1.publisher_id = b2.publisher_id WHERE b2.author_id = 5 and b2.publisher = 23 and MATCH (b1.source) AGAINST ('Sample input string') LIMIT 1;
6.2 Only select the document_id instead of the whole record:
SELECT id FROM books WHERE MATCH (sentence) AGAINST ('This is a sample search input string') and author_id = 5 and publisher_id = 23 LIMIT 1;
- Questions: Are there any other ways that I could try to improve the search speed? According to this doc: https://dev.mysql.com/doc/refman/8.0/en/fulltext-fine-tuning.html I could try adding more stop words, run OPTIMIZE TABLE, move current table to a new one, or upgrading the hardware. But I'm not sure if it's worth to try those methods at all.