MySQL Full-Text Index Performance

Summary: in this tutorial, you will learn how to optimize MySQL Full-Text Index performance during both index creation and search operations.

MySQL full-text index allows you to perform efficient text-based searches on large texts. If you want to achieve optimal performance, you need to understand how to create and utilize full-text indexes effectively.

Creating Full-Text Indexes

1) Choose the right table storage engine

MySQL supports full-text index for both MyISAM and InnoDB tables.

MyISAM is traditionally faster for read-heavy operations, while InnoDB provides better overall transactional support.

Depending on your use case, you can choose the engine that aligns with your performance requirements:

ALTER TABLE your_table
    ADD FULLTEXT index_name (column1, column2);Code language: SQL (Structured Query Language) (sql)

2) Optimize for InnoDB

If you use the InnoDB tables, you should consider adjusting the innodb_ft_sort_pll_degree variable to control the parallelism degree of the sorting operation during index creation. You can experiment with different values to find the optimal setting for your specific workload.

-- Example: Setting the parallelism degree
SET GLOBAL innodb_ft_sort_pll_degree = 4;Code language: SQL (Structured Query Language) (sql)

3) Batch Indexing

If you have large datasets, you should consider breaking down the indexing process into smaller batches. This can help manage system resources more efficiently and prevent potential timeouts:

CREATE FULLTEXT INDEX index_name
    ON your_table (column1, column2)
    WITH PARSER ngram;

Improving Full-Text Search Performance

1) Optimize Query Structure

When performing a full-text search, you should craft your queries that utilize prefixes and phrase searches for better results:

SELECT * FROM your_table
    WHERE MATCH(column1, column2) 
    AGAINST ('"your keyword"' IN BOOLEAN MODE);Code language: SQL (Structured Query Language) (sql)

2) Use Boolean Full-Text Searches

The BOOLEAN mode allows you to use operators such as AND, OR, and NOT. It helps you to have more precise and flexible search queries.

-- Example: Boolean full-text search
SELECT * FROM your_table
    WHERE MATCH(column1, column2) 
    AGAINST ('+keyword1 -keyword2' IN BOOLEAN MODE);Code language: JavaScript (javascript)

3) Adjust the Minimum Word Length

Modify the ft_min_word_len configuration variable to control the minimum word length considered in the full-text index. Smaller values might result in more relevant search results.

-- Example: Setting minimum word length
SET GLOBAL ft_min_word_len = 3;Code language: SQL (Structured Query Language) (sql)

4) Monitor and Optimize Index Size

You should regularly monitor the size of full-text indexes because large indexes can impact search performance.

To check the index’s size, you can use the SHOW TABLE STATUS statement. For example, the following statement displays the index sizes of the posts table:

SHOW TABLE STATUS LIKE 'posts'\GCode language: SQL (Structured Query Language) (sql)

Output:

*************************** 1. row ***************************
           Name: posts
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 10
 Avg_row_length: 1638
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 11
    Create_time: 2023-11-25 11:10:23
    Update_time: 2023-11-25 11:10:28
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)Code language: CSS (css)

Sometimes, you should consider rebuilding indexes periodically or adjusting the ft_max_word_len configuration variable:

SET GLOBAL ft_max_word_len = 20;Code language: SQL (Structured Query Language) (sql)

Summary

• Create a full-text index on the columns properly and tune configuration variables to enhance the performance of the full-text searches.
• Form the full-text search queries that effectively use the full-text index to improve the search performance.