In MySQL, like fuzzy queries are a common way of querying, but in some cases it can cause performance problems. This article will introduce five methods to optimize like fuzzy queries in MySQL.
1. Suffix matching to index
If the field is an index, like fuzzy query can go through the index if the suffix matches. For example, in the query field,johnFor ending records, you can use the following statement:
SELECT * FROM table_name WHERE field_name LIKE '%john';
This method recommends that you try to use suffix matching when querying, that is, the field prefix is fixed and the fuzzy matching of the suffix can be used efficiently.
2. Reverse index
MySQL cannot go through the index when prefix fuzzy matching is required. In this case, the reverse index method can be used to optimize query performance. The specific steps are as follows:
- Create a reverse index field, invert the string of the original index field and save it into the field. For example, if the original index field is
username, a new field can be createdreverse_username,WillusernameThe string in the inverted string is savedreverse_username。 - Add an index to the reverse index field.
- When performing a prefix fuzzy matching query, perform a suffix fuzzy matching query on the reverse index field. For example, if you want to query
abcFor records at the beginning, you can use the following statement:
SELECT * FROM table_name WHERE reverse_username LIKE '%cba';
It should be noted that when inserting or updating data, the reverse index field must be maintained synchronously.
3. Reduce the search scope
Even fuzzy suffix matching is not as fast as precise querying using indexes. Therefore, it is recommended to add other conditions as much as possible when querying to narrow the search range to improve performance. For example:
SELECT * FROM table_name WHERE field_name LIKE '%john' AND other_field = 'some_value';
4. Use cache
For some fields with very frequent queries, caches like REDIS can be used to improve query performance. However, some infrequently changing data should be placed in the cache as much as possible, so that the hit rate is high.
5. With the help of full-text search engine
Fuzzy matching of pre-suffixes can be performed with full-text search engines such as Elasticsearch (ES). The general approach is to put the conditional fields to be queried and some key fields (such as ids) in the full text search engine, and then when performing fuzzy matching, first get the key fields in the full text search engine, and then use the key fields to query in the database.
This is the article about the optimization method of like fuzzy query in MySQL. For more related contents of MySQL like fuzzy query, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!