I'm a little confused about a situation in which SQLite doesn't seem to use existing indexes unless ANALYZE is ran beforehand: https://gist.github.com/martinklepsch/c5987fdce5a3ad830264e39b22968bca

If anyone has experience with this I'd appreciate your input 🙂

is it only analyze that causes the index to be used? or if you do an insert or something does it cause it to be used?

@martinklepsch It may depend on the version of SQLite you're using. Also, SQLite's like is case-insensitive by default so it won't use the index unless a) the index is case-insensitive or b) analyze determines that case doesn't matter. I'm guessing at b) based on various SO answers.

See for example https://stackoverflow.com/questions/12994870/sqlite-not-using-index-with-like-query

interesting - I thought I tried without the like clause but your findings make me doubt myself 😀