LIKE in SQLite (and even MysQL)

Question

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

I would understand that a wildcard may not use an index, but what about the beginning begins with a 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 

While the wildcard is at the beginning, the index cannot be used. If you have fixed data before the template, you can use the index.

Some db, such as PostgreSQL, have tricks that allow you to use indexes in all cases, but MySQL and SQLite do not.

Answer 2

This may help you: How MySQL uses indexes .