SoFunction
Updated on 2025-05-12

Optimization solution for like fuzzy query in MySQL

In MySQL,LIKEFuzzy queries are very common, but they can cause performance problems, especially when there is a large amount of data.LIKEQueries usually result in full table scans because it cannot take advantage of the index (especially when the matching pattern starts with a wildcard). However, forLIKEThere are some common techniques for optimizing queries that can help improve query efficiency.

1. Avoid queries starting with wildcards

  • existLIKEIn query, if the pattern is%Starting, for example:
SELECT * FROM users WHERE name LIKE '%john';
  • This query results in full table scans because the index cannot be used. In this case, MySQL checks all rows for matching results.

Optimization suggestions: Try to avoid%The query at the beginning. If possible, refactor the query to avoid this pattern. For example, use prefix matching:

SELECT * FROM users WHERE name LIKE 'john%';

In this way, MySQL can use indexes to speed up queries, especially innameIf there is an index on the field.

2. Use Full-text Index

  • For scenarios where full-text search is required, MySQL provides a full-text index (FULLTEXT), it is especially suitable for processing text dataLIKEQuery (especially fuzzy queries for long text).

Optimization suggestions: If you need to execute on longer text fieldsLIKEFor query, you can consider using full-text index. For example:

ALTER TABLE articles ADD FULLTEXT (content);

Then, you can useMATCHandAGAINSTSyntax to query, notLIKE：

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

Notice：FULLTEXTIndex is suitable for MyISAM and InnoDB storage engines, but in InnoDB,FULLTEXTIndexes are only available for MySQL 5.6 and above.

3. Use prefix index

  • If you know that the content of a query is usually searched based on the prefix of a certain field, you can use the prefix index. This allows MySQL to create indexes on the first few characters of the index, thus speeding up queries.

Optimization suggestions: When creating an index, you can use the prefix length to limit the size of the index, for example:

CREATE INDEX idx_name ON users(name(10));

This means that the index is based onlynameThe first 10 characters of the field are indexed. This optimization can be helpful if you know that most queries are searched based on the first few characters of a field.

4. Avoid fuzzy queries on large data sets

  • If the query data set is very large, useLIKEQuerying will cause performance bottlenecks. At this time, consider partitioning or other optimization methods of the data set to reduce the amount of data scanned.

Optimization suggestions: Consider partitioning the table to make the query scope smaller and improve query efficiency. For example:

CREATE TABLE users (
    id INT,
    name VARCHAR(255),
    date_of_birth DATE
)
PARTITION BY RANGE (YEAR(date_of_birth)) (
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (2010),
    PARTITION p2 VALUES LESS THAN (2020)
);

5. Use REGEXP (regular expression) instead of LIKE

  • In some cases, regular expressions (REGEXP) Possibly better thanLIKEMore efficient, especially in complex pattern matching.

Optimization suggestions: When the query requires very complex matches, useREGEXPCompareLIKEMore suitable. For example:

SELECT * FROM users WHERE name REGEXP '^john';

This also helps to optimize some complex fuzzy matching patterns.

6. Ensure that the query field has a suitable index

  • For frequent participationLIKEQuery fields to ensure that there are indexes on these fields. AlthoughLIKEQueries cannot rely entirely on indexes, but if you can optimize queries, avoid like'%term%'This way, using wildcard queries, indexes can still help.

Optimization suggestions: Used if neededLIKECreate indexes on fields that are queryed, but avoid using them before and after fields.%Wildcard characters.

7. Use cache

  • For some duplicate fuzzy queries, caching results may be an effective optimization method. You can consider using caching tools such as Redis or Memcached to cache the query results to avoid querying the database every time.

Optimization suggestions: Use a cache system to store the results of frequently queried, especially for fields or data that are not updated frequently.

8. Process query in batches

  • If you need to do very complexLIKEQuery, consider processing the query results in batches. For example, paging queries can reduce the amount of data per query and reduce database pressure.

Optimization suggestions:useLIMITandOFFSETCome to query a large amount of data in batches:

SELECT * FROM users WHERE name LIKE 'john%' LIMIT 100 OFFSET 200;

Summarize

optimizationLIKEThe basic idea of ​​query is to reduce the number of scans in the entire table and use indexing, caching and other technologies to improve query efficiency. Try to avoid%The pattern at the beginning uses appropriate indexes and full-text indexes, and utilizes other technologies such as regular expressions and partitions when needed. With these methods, you can improve query efficiency in large amounts of data.

The above is the detailed content of the optimization solution for like fuzzy query in MySQL. For more information about MySQL like fuzzy query optimization, please pay attention to my other related articles!