Skip to content

LOCATE function for index-friendly pattern matching

The LOCATE function is a SQL string function used to determine the position of the first occurrence of a substring within a string^[600-developer__database__sql-like-index.md]. It is typically utilized to enable index usage for prefix searches that might otherwise result in full table scans^[600-developer__database__sql-like-index.md].

Pattern Matching and Index Usage

Standard SQL pattern matching using the LIKE operator has specific behaviors regarding index usage^[600-developer__database__sql-like-index.md]:

  • Leading Wildcard (%keyword): Queries with a wildcard at the beginning of the pattern cannot use a standard index^[600-developer__database__sql-like-index.md].
  • Trailing Wildcard (keyword%): Queries with a leading constant and a trailing wildcard can use an index^[600-developer__database__sql-like-index.md].
  • Enclosed Wildcard (%keyword%): Queries with wildcards on both sides cannot use an index^[600-developer__database__sql-like-index.md].

Using LOCATE

The LOCATE function provides an alternative syntax for searching, accepting the substring and the string as arguments^[600-developer__database__sql-like-index.md].

select * from table where columnName locate('keyword' , table.field) > 0

This function returns the index position of the first occurrence of the keyword^[600-developer__database__sql-like-index.md]. It is often cited as a way to rewrite queries to "take the index" (走索引), particularly in scenarios where the search pattern is not a simple leading-constant match^[600-developer__database__sql-like-index.md].

Alternatives

Another strategy for handling suffix searches (leading wildcards) involves using a reverse index^[600-developer__database__sql-like-index.md]. In such cases, a query can be rewritten by reversing both the field value and the search pattern^[600-developer__database__sql-like-index.md]:

select * from table where columnName and reverse(table.field) like reverse('%keyword')

Sources

^[600-developer__database__sql-like-index.md]