SQL LIKE pattern indexing¶

SQL LIKE pattern indexing refers to the database optimization strategies regarding the usage of indexes when querying data with the LIKE operator.^[600-developer-database-sql-like-index.md]

While standard indexes are typically used to speed up queries, their effectiveness with LIKE clauses depends heavily on the placement of the wildcard characters (%) in the search pattern^[600-developer-database-sql-like-index.md].

Index Usage Rules¶

Whether a database utilizes an index for a LIKE query is determined by the structure of the pattern:

Leading wildcard match: Queries formatted as LIKE 'keyword%' will use the index^[600-developer-database-sql-like-index.md].
Leading and trailing wildcard match: Queries formatted as LIKE '%keyword%' cannot use the index^[600-developer-database-sql-like-index.md].
Trailing wildcard match: Queries formatted as LIKE '%keyword' generally do not use a standard index^[600-developer-database-sql-like-index.md].

Performance Optimizations¶

For scenarios where a query ends with a wildcard (e.g., '%keyword'), which typically prevents index usage, specific strategies can be employed to regain performance.

Reverse Indexes¶

If a column is indexed with a reverse index, a query ending in a keyword can be optimized^[600-developer-database-sql-like-index.md]. The logic relies on reversing both the field data and the search string:

The original query is LIKE '%keyword'.
It can be transformed to compare the reversed field against the reversed pattern^[600-developer-database-sql-like-index.md].

Function Rewrites¶

Another method to handle pattern matching is to rewrite the query using the LOCATE function (or equivalent string position functions)^[600-developer-database-sql-like-index.md]. This approach may enable index usage depending on the database system and function implementation^[600-developer-database-sql-like-index.md].

[[Query Optimization]]
[[Database Indexes]]

Sources¶

^[600-developer-database-sql-like-index.md]