Skip to content

Reverse index pattern

The Reverse index pattern is a database optimization technique used to support efficient querying on strings with wildcard prefixes.^[600-developer-database-sql-like-index.md]

In standard SQL LIKE queries, an index is typically bypassed if the pattern begins with a wildcard (e.g., %keyword), resulting in a full table scan.^[600-developer-database-sql-like-index.md] To address this, the pattern involves storing a reversed version of the target column value in a separate index (or computing the reversal at query time).^[600-developer-database-sql-like-index.md]

Mechanism

When a column is indexed in reverse, a search for a suffix in the original string becomes a search for a prefix in the reversed string.^[600-developer-database-sql-like-index.md] For example, querying for LIKE '%keyword' (which typically cannot use a standard index) can be transformed into a query on the reversed field looking for LIKE 'drowyey%', allowing the database engine to utilize the index range scan.^[600-developer-database-sql-like-index.md]

Usage Example

Instead of a non-indexable query: 

SELECT * FROM table WHERE columnName LIKE '%keyword';

A developer can apply the reverse pattern, often by reversing the column value during comparison^[600-developer-database-sql-like-index.md]:

SELECT * FROM table WHERE columnName AND reverse(table.field) LIKE reverse('%keyword');

This transformation enables the database to perform an index seek rather than a full scan.^[600-developer-database-sql-like-index.md]

  • [[Database Indexing]]
  • [[Query Optimization]]
  • [[SQL]]

Sources

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