Improve MySQL InnoDB Full-text Search Performance

user_8540391 0
Viewed 57

  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;
  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.
database-performance full-text-search innodb query-optimization mysql
1 Answers

  • Toss the short words and any stop words

  • Prefix the rest with '+' and add "IN BOOLEAN MODE"

    AGAINST ('+sample +search +input +string' IN BOOLEAN MODE)

If you need the words exactly like your LIKE, then do

WHERE MATCH(..) AGAINST(...)
  AND ... LIKE '%This is a sample search input string%'
  AND ...

The FULLTEXT search will be fast, but may find documents that have the words in some other order. The LIKE will be fast because it is only checking only a few rows (the ones that FT found).

user_17668310
Related