How to Optimize MySQL Queries with LIKE Clauses Using Composite Indexes?

Identifying the Optimal Indexing Strategy for Queries Containing LIKE Clauses

When optimizing database queries involving LIKE clauses, the effectiveness of indexes becomes somewhat uncertain. However, understanding the limitations and optimal indexing strategies can significantly improve query performance.

Consider the following query:

SELECT name, usage_guidance, total_used_num
FROM tags
WHERE
 ( name LIKE CONCAT('%', ?, '%') OR
   usage_guidance LIKE CONCAT(?, '%') )
 AND name NOT IN ($in)
ORDER BY name LIKE CONCAT('%', ?, '%') DESC, name ASC
LIMIT 6

Determining the most appropriate index for this query requires considering the nature of LIKE clauses. MySQL utilizes B-tree indexes for column comparisons using operators like = and >. While B-tree indexes can enhance LIKE comparisons when the LIKE argument is a constant string without wildcards, the presence of wildcards limits their effectiveness.

In the query above, neither of the LIKE expressions begin with wildcards. This means that a composite index on the columns usage_guidance and name would be the most beneficial. By ordering the index in this way, the query can take advantage of the index for both LIKE comparisons.

Here is an SQL command to create the composite index:

CREATE INDEX idx_tags ON tags(usage_guidance, name);

In cases like this, where multiple factors affect query performance, analyzing the table structure, sample data, and expected output can often reveal additional opportunities for optimization.

The above is the detailed content of How to Optimize MySQL Queries with LIKE Clauses Using Composite Indexes?. For more information, please follow other related articles on the PHP Chinese website!