Can ordering of conditions optimize query speed?
(1) By anonymous on 2020-12-16 20:14:22 [source]
SQL only defines the result set. The actual method of producing that set is left to the engine. What I'd like to know for SQLite3 in particular is if there is any benefit to re-ordering conditions in a WHERE statement.
Every additional condition cuts down the result set further. So, if one puts first those conditions that would cut down the most, then each further condition will have less and less data to work on, will it make a performance difference compared to an arbitrary ordering?
(2) By Warren Young (wyoung) on 2020-12-16 20:31:09 in reply to 1 [link] [source]
SQLite's query planner tries to figure out such things for you, but it might not be clever enough or have enough information to do it for you.
The best tools for figuring out whether SQLite is doing things in the optimal way are EXPLAIN QUERY PLAN and .expert. Once you understand what those tools are telling you, they'll answer the rest of your questions.