LIKE in SQLite (and even MysQL)

Question

Will the LIKE keyword use the index in SQLite and/or MySQL?

I would understand that a wildcard match might not use the index, but how about a starts with comparison?

Answer 1

It depends:

WHERE field1 LIKE 'test'  << can use index
WHERE field1 LIKE 'test%' << can also use index
WHERE field1 LIKE '%test' << cannot use index
WHERE field1 LIKE '_test' << cannot use index

As long as the wildcard is at the start, no index can be used. If you have fixed data before a wildcard, then an index can be used.

Some db's such as PostgreSQL have tricks that allow the use of indexes in all cases, but MySQL and SQLite do not.

Answer 2

This might help you: How MySQL Uses Indexes.