Skip to content

SQL LIKE pattern matching and indexing

In SQL database queries, the usage of the LIKE operator for pattern matching directly impacts whether the database engine can utilize an index to retrieve data^[600-developer__database__sql-like-index.md]. While indexes can significantly speed up data retrieval, they are effective only when the search pattern allows the database to perform a lookup rather than a full table scan^[600-developer__database__sql-like-index.md].

Index Utilization Rules

The efficiency of a LIKE query depends heavily on the placement of the wildcard character (%).

  • Leading Wildcards: Queries such as LIKE '%keyword' or LIKE '%keyword%' generally prevent the use of standard indexes^[600-developer__database__sql-like-index.md].
  • Trailing Wildcards: A pattern where the wildcard is only at the end, such as LIKE 'keyword%', allows the query to use an index^[600-developer__database__sql-like-index.md].

Optimization Techniques

To improve performance on queries that would otherwise result in full table scans, specific query rewrites or index strategies can be employed^[600-developer__database__sql-like-index.md].

Reverse Indexes

For queries with a trailing wildcard (e.g., LIKE '%keyword'), standard indexing fails. However, if the database column uses a reverse index, the query can effectively utilize the index^[600-developer__database__sql-like-index.md]. This is conceptually similar to rewriting the query to compare a reversed field against a reversed pattern^[600-developer__database__sql-like-index.md].

Function-based Rewrites

Queries using the LOCATE function (finding the position of a substring) can also be optimized to use indexes, provided the query is structured to allow index access^[600-developer__database__sql-like-index.md].

Sources

  • 600-developer__database__sql-like-index.md