To improve the full-text search performance of MySQL requires multiple optimizations from index, configuration, query statements and external tools. 1. Use full-text index reasonably, avoid redundant fields and whole table indexes, use prefix indexes appropriately and customize stop words; 2. Adjust the full-text cache size of system parameters such as ft_min_word_len and InnoDB, and execute OPTIMIZE TABLE regularly; 3. Optimize query statements, limit wildcard usage, add filter conditions, and cooperate with LIMIT paging; 4. High-demand scenarios can introduce external search engines such as Elasticsearch to improve capabilities and reduce the burden on MySQL.

Although MySQL's full-text search function is convenient, if the amount of data is large or the query is frequent in actual use, performance problems will be easily exposed. If you want to improve the performance of MySQL full-text search, you cannot rely solely on the default configuration. You need to start from multiple aspects such as indexing, querying statements, and structural design.

Rationally use and optimize full-text indexing

Full-text index is the basis of full-text search, but it cannot be efficient if it is built. First, avoid creating full-text indexes on uncommonly used fields, which will waste resources. Secondly, you can consider using prefix indexing, especially for longer text fields, such as VARCHAR(1000) , which does not necessarily require all characters to participate in the indexing.

• It is recommended to set an appropriate prefix length according to the actual content length.
• If the search keyword is generally short, setting the prefix to 255 or 500 is enough.
• It is not recommended to add full text indexes to all text fields in the entire table. It should be selected according to the actual search requirements.

In addition, MySQL's full-text index ignores some stopwords by default, such as "the" and "is" and other common words. If these words in your data make sense, you can customize the stop word list to avoid unnecessary filtering or omissions.

Adjust full-text search parameters to improve efficiency

MySQL provides some system variables for controlling the behavior of full-text searches, and appropriate adjustments to them can lead to significant performance improvements:

• ft_min_word_len : Sets the minimum indexable word length, the default is 4. If you have many 3 words in your application (such as Chinese pinyin or abbreviation), you can change it to 3, but be careful that this will increase the index volume.
• innodb_ft_cache_size and innodb_ft_total_cache_size : These two parameters control the size of the full-text index cache. For frequently updated tables, increasing the cache can reduce disk IO.
• innodb_optimize_fulltext_only and OPTIMIZE TABLE : Performing optimization operations regularly can clean up fragments and improve query efficiency, especially after a large number of additions, deletions and modifications.

These parameters can be modified in the configuration file or dynamically set through SQL, but it is recommended to verify the effect in the test environment first.

Write the query statement to avoid invalid scans

Many performance problems actually lie in the query statement itself. For example, when using IN BOOLEAN MODE mode, using wildcards without restrictions or being too broad will cause the engine to make a lot of invalid matches.

For example:

 SELECT * FROM articles WHERE MATCH(content) AGAINST('database*' IN BOOLEAN MODE);

Although this statement uses wildcard characters, if the result set is too large, it may be slower than exact match. At this time, you should consider adding additional filtering conditions, such as time range, classification ID, etc., to narrow the search range.

Another common problem is that it does not paginate and returns thousands of results directly. In this case, the database will be under great pressure. It is recommended to always use it with LIMIT , and for front-end display, users will basically not turn to too many pages.

More efficient with external search engines (if applicable)

If your business requires high search experience, such as supporting fuzzy matching, relevance sorting, high concurrent queries, etc., MySQL's full-text search may not be powerful enough. At this time, you can consider introducing professional search engines such as Elasticsearch or Sphinx.

Benefits include:

• Supports more complex query syntax and more flexible correlation algorithms.
• It can synchronize data asynchronously to reduce the burden on MySQL.
• Supports distributed deployment to deal with large-scale data and high-concurrency scenarios.

Of course, this also means that additional services are required to be maintained, suitable for projects of a certain scale.

Basically that's it. To improve the full-text search performance of MySQL, the key is to use indexes reasonably, optimize configuration, write efficient query statements, and combine them with external tools to expand their capabilities if necessary. These details don't seem complicated, but they are easily overlooked in practical applications.

The above is the detailed content of Improving MySQL full-text search performance. For more information, please follow other related articles on the PHP Chinese website!