4 ms·

"Query planner are what make SQL database engines so amazingly useful and powerful. (This is true of all SQL database engines, not just SQLite.)" No it isn't.

"Query planner are what make SQL database engines so amazingly useful and powerful. (This is true of all SQL database engines, not just SQLite.)" 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.

deleted 13y ago
[deleted]
ww520 13y ago
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?
chris_wot 13y ago
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.

deleted 13y ago

[deleted]

ww520 13y ago

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?

chris_wot 13y ago

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.