LIKE in SQLite (and even MysQL)

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

I would understand t开发者_如何学Gohat a wildcard match might not use the index, but how about a starts with comparison?

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.

This might help you: How MySQL Uses Indexes.