> The main takeaway from this for me is that SQLite’s query planner seems to be pretty limited.
This doesn't appear to be true at all.
The order of WHERE conditions does not matter; the order of columns in an index does.
Everything you're describing is pretty much just how indexes fundamentally work in all databases. Which is why you're saying it hasn't been "solved" by anyone.
Indexes aren't magic -- if you understand how they work as a tree, it becomes very clear what can be optimized and what can't.
It is true that occasionally query planners get it wrong, but it's also often the case that your query was written in a non-obvious way that is equivalent in terms of its formal results, but is not idiomatic -- and making it more idiomatic means the query planner can more easily understand which indexes to use where.
(copying my reply from the other comment that said the same thing as you)
The order of conditions in a WHERE definitely does matter, especially in cases where the conditions are on non-indexed columns or there are CPU-intensive search operations like regex, string ops, etc.
I just ran this test locally with a table I created that has 50 million rows:
```
» time sqlite3 test.db "select count() from test WHERE a != 'a' AND a != 'b' AND a != 'c' AND a != 'd' AND b != 'c' AND d != 'd' AND e != 'f' AND f = 'g'"
sqlite3 test.db 5.50s user 0.72s system 99% cpu 6.225 total
» time sqlite3 test.db "select count() from test WHERE f = 'g' AND a != 'a' AND a != 'b' AND a != 'c' AND a != 'd' AND b != 'c' AND d != 'd' AND e != 'f'"
sqlite3 test.db 1.51s user 0.72s system 99% cpu 2.231 total
```
The only difference is swapping the `f = 'g'` condition from last to first. That condition never matches in this query, so it's able to fail fast and skip all of the work of checking the other conditions.
Sorry, I should have clarified -- the order of WHERE conditions doesn't matter for whether an index is utilized. I thought that was the context of the original comment, but now I realize maybe it was unclear.
Yes, of course you can skip evaluating other conditions if an AND fails and that can affect speed. So that's the same as most programming languages.
This doesn't appear to be true at all.
The order of WHERE conditions does not matter; the order of columns in an index does.
Everything you're describing is pretty much just how indexes fundamentally work in all databases. Which is why you're saying it hasn't been "solved" by anyone.
Indexes aren't magic -- if you understand how they work as a tree, it becomes very clear what can be optimized and what can't.
It is true that occasionally query planners get it wrong, but it's also often the case that your query was written in a non-obvious way that is equivalent in terms of its formal results, but is not idiomatic -- and making it more idiomatic means the query planner can more easily understand which indexes to use where.