Skip to content

Reverse index optimization

Reverse index optimization is a database technique used to improve query performance for specific SQL LIKE pattern matching scenarios.^[600-developer__database__sql-like-index.md]

Standard LIKE Behavior

In standard SQL queries using the LIKE operator, the ability to utilize an index depends heavily on the placement of the wildcard character (%).^[600-developer__database__sql-like-index.md]

  • Leading Wildcard: Queries such as SELECT * FROM table WHERE columnName LIKE '%keyword' generally cannot use a standard index.^[600-developer__database__sql-like-index.md]
  • Trailing Wildcard: Queries where the keyword is at the beginning, such as LIKE 'keyword%', are able to leverage standard indexes.^[600-developer__database__sql-like-index.md]
  • Enclosed Wildcard: Patterns with wildcards on both ends, such as LIKE '%keyword%', also typically result in full table scans and do not use standard indexes.^[600-developer__database__sql-like-index.md]

Optimization Strategy

To enable indexing for queries with trailing wildcards (e.g., %keyword), the data can be stored in a reversed format.^[600-developer__database__sql-like-index.md] By creating a reverse index on the column, a query for a suffix in the original string becomes a query for a prefix in the reversed string.^[600-developer__database__sql-like-index.md] For example, searching for '%keyword' on the original field is equivalent to searching for 'drowyek%' on the reversed field, allowing standard index B-Tree traversal to work effectively.^[600-developer__database__sql-like-index.md]

Implementation

Implementation typically involves explicitly applying a reverse function during the query.^[600-developer__database__sql-like-index.md]

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

Alternatively, the LOCATE function can sometimes be used to achieve index utilization in these scenarios.^[600-developer__database__sql-like-index.md]

SELECT * FROM table WHERE columnName LOCATE('keyword' , table.field) > 0

Sources

  • 600-developer__database__sql-like-index.md