SQLite Forum

Usage of suboptimum index
Login
I see. Managed to miss that. You're correct of course, according to your stats:

        Relations|Relations_Index2|80965 80965 80965 1 1
        Relations|Relations_Index1|80965 1 1 1 1

both of these:

        WHERE TypeFirstDatum=? AND TypeSecondDatum=? AND IDFirstDatum=?
        WHERE IDFirstDatum=?

should return on average 1 row. Technically it's "on average", not "at most",
so as far as SQLite can tell there might be some values that match more than
one key.

So SQLite calculates the cost of using each index as pretty much equal.
However, it has a heuristic that says that if one index uses a superset of the
WHERE terms used by another, it must be better:

[](https://www.sqlite.org/src/artifact/2ea911238674e?ln=1999..2010)

You could argue that this heuristic is counter-productive - if both indexes
will match a single row, why not use the shorter one and save a little CPU
doing key-comparisons (also, in other cases but not this one, you might save
some IO as shorter indexes take up less space on disk)? I think the answer
is that (a) those stats are only averages, not hard guarantees and (b) the
stats might be out of date - they were current when ANALYZE was run but things
might have changed since. So SQLite figures using the index that covers more
fields is a better bet.

Dan.