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

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:

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

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

CopySELECT * 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 .