Skip to content

Prefix matching optimization

Prefix matching optimization is a database query optimization technique focused on structuring search patterns to utilize standard indexes, specifically when handling LIKE conditions in SQL^[600-developer-database-sql-like-index.md].

Implementation

The optimization relies on the position of the wildcard character (%) within the LIKE clause^[600-developer-database-sql-like-index.md].

  • Supported: Queries where the keyword is followed by a wildcard (e.g., LIKE 'keyword%') allow the database to traverse the index structure, as the search starts from the beginning of the string^[600-developer-database-sql-like-index.md].
  • Unsupported: Queries with a leading wildcard (e.g., LIKE '%keyword' or LIKE '%keyword%') generally prevent index usage because the sorting mechanism of standard indexes does not align with searching for an unknown prefix^[600-developer-database-sql-like-index.md].

Advanced Scenarios

To handle trailing wildcard searches (e.g., matching a keyword at the end of a string), specific architectural changes or query rewrites may be employed^[600-developer-database-sql-like-index.md]:

  • Reverse Indexes: If the database column is indexed in reverse, a query matching the suffix of the original text can leverage this index^[600-developer-database-sql-like-index.md].
  • Rewriting with Functions: The query can potentially be rewritten using functions like LOCATE to detect the position of a substring, though this approach requires careful implementation to maintain performance^[600-developer-database-sql-like-index.md].
  • [[Database Indexing]]
  • [[SQL Query Optimization]]

Sources

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