Hi Rowan, thank you for your kind support. You grasped the essence of my questions. :-) I'm using SQLite 3.25.00.
Thank you, Gerlando On Tue, Feb 5, 2019 at 9:59 AM Rowan Worth <row...@dug.com> wrote: > On Tue, 5 Feb 2019 at 16:06, Simon Slavin <slav...@bigfraud.org> wrote: > > > On 5 Feb 2019, at 8:00am, Gerlando Falauto <gerlando.fala...@gmail.com> > > wrote: > > > > > Thank you for your explanations guys. All this makes perfect sense. > > > I still can't find a solution to my problem though -- write a query > that > > is guaranteed to return sorted results, in some optimal way. > > > > Please state your table definition, and desired query including ORDER BY > > clause. Please also tell us whether the amount of space taken up by your > > database file is important. Then we will tell you how to make SQLite use > > an efficient way to arrive at your desired result. > > > > The table definition was literally the first thing in Gerlando's initial > email, and the desired query has also been clarified. But I assume you > didn't actually read the thread before commenting; if you had you would > have also noticed that Gerlando was the first person to note that it isn't > reliable to depend on the order of results coming out of a SELECT which > doesn't have an ORDER BY clause. > > IMO it would be great if we could all move on from that well established > fact and focus on the issue Gerlando is trying to raise. We have this > query: > > SELECT source1, source2, ts, value > FROM rolling > WHERE source1 = 'aaa' > AND ts > 1 AND ts < 100000000 > ORDER BY source1, source2, ts; > > And this index: > > CREATE INDEX `sources` ON `rolling` ( > `source1`, > `source2`, > `ts` > ); > > What is stopping sqlite's query planner from taking advantage of the index, > which it has chosen to use for the query, to also satisfy the ORDER BY? > Instead adds an extra TEMP B-TREE step to sort the results, which slows > things down. Intuitively it seems there's a potential for optimisation > here. Which doesn't mean it's feasible, but it would be a pretty good win > to be able to provide ORDER BY for free in more circumstances so it's worth > considering. > > Gerlando, what version of sqlite are you using? > > -Rowan > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
