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
