Imporve MySQL InnoDB Full-text Search Performance

Question

1.Background:

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

2. 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;

3. 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;

4. 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.

5. 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"

6. 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;

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

Answer 1

The more words you have in the string, the more rows will match. This means more rows to score and sort.

I tried a similar test; it returned more than half the rows.

Then I changed it this way:

• Got rid of short words;
• Got rid of common words, hopefully including words that are in the default stoplist;
• Put + in front of the remaining words;
• Added IN BOOLEAN MODE

This time it ran faster and returned only 2% of the rows. (This comes closer to matching the LIKE test.)

(In my opinion, SHOW PROFILE is useless -- 90+% of the time is spent in a few meangless actions such as "initializing" or "sending data".)

When the WHERE clause contains both MATCH and some regular tests (eg, author_id = 5), the Optimizer probably starts with MATCH on the presumption that it will lead to fewer rows, then further filters based on the other clauses. That is, unlike other indexing, there is no way to make a useful "composite" index.

Note: If you need a certain phrase to match, do the technique I discuss above, then add

AND ... LIKE '%The exact phrase%'

What happens this time is

1. Do the Fulltext search;
2. Run the LIKE against the 2% [in my case] that were found -- this makes the LIKE sluggishness moot since it is used for 2% instead of 100% of the rows.