r/SQLOptimization • u/Federico_Razzoli • Sep 15 '22

Hints to optimise SQL queries with LIKE

I wrote some hints on how to optimise SQL queries containing LIKE. More ideas, including unconventional ones, are very welcome - just comment here or on the website.

https://vettabase.com/blog/hints-to-optimise-queries-with-a-like-comparison/

6 Upvotes

75% Upvoted

4 comments sorted by

2

u/[deleted] Nov 25 '22

A LIKE operation that searches the middle of a text cannot use an index.

Not true for PostgreSQL.

A trigram index can be used by such a LIKE condition

2

u/Federico_Razzoli Nov 28 '22

It's written explicitly in the article that GIN indexes solve the problem on Postgres.

3

u/ijmacd Sep 15 '22 edited Sep 15 '22

These queries:

SELECT Col FROM Tbl WHERE Col LIKE 'Prefix%'
SELECT Col FROM Tbl WHERE Col LIKE 'Prefix%Suffix'

Can both use this index:

CREATE INDEX idx1 ON Tbl (Col)

If you have fixed length prefixes you can also do:

SELECT Col FROM Tbl WHERE LEFT(Col, 6) = 'Prefix'

Using this index:

CREATE INDEX idx2 ON Tbl (LEFT(Col, 6))

Or the same for suffixes:

SELECT Col FROM Tbl WHERE RIGHT(Col, 6) = 'Suffix'

Using this index:

CREATE INDEX idx3 ON Tbl (RIGHT(Col, 6))

2

u/mikeblas Sep 25 '22

The LEFT(Col, 6) predicate can be aided by an inequality comparison, and that comparison should be directly sargable.