Skip to content

LOCATE function optimization

LOCATE function optimization is a database technique used to enable index scans for SQL queries that search for a substring match at the end of a string, typically when a LIKE operator would otherwise result in a full table scan.^[600-developer-database-sql-like-index.md]

Optimization Scenario

In standard SQL optimization, a LIKE condition with a leading wildcard (e.g., WHERE columnName LIKE '%keyword') cannot use a standard index because the database must inspect every row to find matches.^[600-developer-database-sql-like-index.md] However, by applying the LOCATE function, this query can be rewritten to utilize a reverse index.^[600-developer-database-sql-like-index.md]

Implementation

The optimization involves rewriting the trailing wildcard query to use the LOCATE function.^[600-developer-database-sql-like-index.md] Instead of relying on the pattern match, the query checks if the substring exists (returning a position index greater than 0).^[600-developer-database-sql-like-index.md]

Example Rewrite:

-- Original query (often results in full scan)
SELECT * FROM table WHERE columnName LIKE '%keyword';

-- Optimized query using LOCATE
SELECT * FROM table WHERE LOCATE('keyword', columnName) > 0;

For this rewrite to effectively use an index, the database typically requires the underlying column to use a reverse index structure.^[600-developer-database-sql-like-index.md]

Sources

  • 600-developer-database-sql-like-index.md
  • Index
  • [[SQL]]
  • [[Query Optimization]]