Read more

MySQL: Can I speed up LIKE queries by adding an index?

Henning Koch
August 22, 2012Software engineer at makandra GmbH

For string columns, MySQL indexes the left side of a string. That means an index can speed a like query that has a wildcard on the right side:

SELECT * FROM foo WHERE field LIKE "bar%" # will be faster with an index
Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

It can not speed up a query that has a variable left side:

SELECT * FROM foo WHERE field LIKE "%bar%" # will not be faster with an index

That also means if you use the ancestry gem Show archive.org snapshot you should index your ancestry column if you use scopes like descendants or subtree, which are evaluated using LIKE queries.

Note

In PostgreSQL you an speed up arbitrary LIKE queries by using a trigram index Show archive.org snapshot .

Posted by Henning Koch to makandra dev (2012-08-22 16:26)