Re:SQLite 3.8.0 Release with next-generation query planner
by ww520 on Tuesday August 27, 2013 @03:16AM
(#6281961)
Just curious, what database is that? If the index on id is a B+ tree rather than a hashtable, it's very unlikely id > 100 would be slower. You are issuing one query with it vs. N queries with the 2nd approach.
Were there a lot of qualified rows for id > 100? If there're a million rows qualified for id > 100, which way is faster?
Re:SQLite 3.8.0 Release with next-generation query planner
by chris_wot on Tuesday August 27, 2013 @10:55AM
(#6283781)
The optimiser might not be using an index in the most optimal way. Sometimes you have to guide the optimiser and it may not have enough statistical data to get the best plan. One way of doing this is to rewrite the query. In this case, if I had to hazard a guess, the developers have suggested you break down the where clause from OR conditions to instead use a UNION ALL, which may help influence the optimizer to choose the most optimal plan.
Adding statistics or an index may help. Just understand that if your data distribution changes radically the index or rewritten query may no longer be valid.
No it isn't. I have been using SQL databases which got absolutely horrible query optimizer totally ruining any performance exceptations. I have blogged about that, and I was very frustrated and disappointed with results.
Which is faster if id>100 or simply walking database by using series of selects select ... id=101 ... select ... id=102 ... Turns out that the later method is a much faster. Engineers also then suggested that I could optimize the query by using structure select ... id=101 UNION select ... id=102. That's simply insane. How ever the query was written, query otpimizer / planner should make sure that all these queries are delivered using similar background process. Of course that walk is impossbile to optimizer, because it's series of individual queries. But it's just crazy that select id>100 is slower than that!
Yes, you might have guessed that the SQL database I'm referring to isn't SQLite.
And finally, I love SQLite and use it for most of my (work & hobby) projects as local database.