SQLite Forum

Automatic indexing (idle question)
The problem with indexing every column is that this is actually an anti-pattern in most cases.

Indexes are an optimization tool, and almost always have a trade-off. They make some operation, a lookup, faster at the cost of space (which itself can slow some things down), and the need to update the index for every database update.

Update frequency is a very important factor for deciding on indexes. If you rarely lookup by the value of a column, but the table is updated a lot, it may be faster to just do the table scan to do that lookup, especially if the table size is somewhat limited because most of the updates are changing values rather than adding new values (or we are doing a lot of deletes also).

On the other hand, a very static table that is updated rarely, but looked up a lot can benefit a lot from a good set of indexes, and good table statistics for the planner.

So, not only do you need to run all the select queries that you will use, but you also need a good representation of the updates you will be doing so you can get an estimate of the full cost for each index.