Yes, sqlite uses only one index per table in FROM clause... Except for when it can use the OR optimization [1]
> Left to right, no skipping, stops at the first range
I don't know if we need a soundbite for this, nor is it sqlite specific. This is a direct consequence of the fact that indexes are basically just sorted lists you can binary search over. They are sorted left to right (you have to choose some order anyhow) meaning all the other orderings cannot avail of the speedup, and thus sqlite chooses to not use it.....
Except if left column of index is measured to be low cardinality by ANALYZE [2]'s output in the sqlite_stats table, in which case it is ok to scan over it and then binary search right column. More at skip-scan optimization [3].
Yes, sqlite uses only one index per table in FROM clause... Except for when it can use the OR optimization [1]
> Left to right, no skipping, stops at the first range
I don't know if we need a soundbite for this, nor is it sqlite specific. This is a direct consequence of the fact that indexes are basically just sorted lists you can binary search over. They are sorted left to right (you have to choose some order anyhow) meaning all the other orderings cannot avail of the speedup, and thus sqlite chooses to not use it.....
Except if left column of index is measured to be low cardinality by ANALYZE [2]'s output in the sqlite_stats table, in which case it is ok to scan over it and then binary search right column. More at skip-scan optimization [3].
[1] https://www.sqlite.org/optoverview.html#evaluating_or_constr...
[2] https://www.sqlite.org/lang_analyze.html
[3] https://www.sqlite.org/optoverview.html#the_skip_scan_optimi...