Thank you Luuk, I understand your point. However, the query plan already takes advantage of the index and should be retrieving data in that order. Reading the docs https://www.sqlite.org/optoverview.html#order_by_optimizations my understanding was that SQLite would be taking advantage of that. So perhaps my use case it's too complicated (many columns -- some filtered, some not -- skip/scan, all together) to make it obvious to the query planner that data *is* already sorted. Or maybe it never occurred to anyone that someone might be trying to do something like that. Or (most likely) my understanding of how data is retrieved is plain wrong...
Thank you! Gerlando On Mon, Feb 4, 2019 at 1:26 PM Luuk <luu...@gmail.com> wrote: > > On 3-2-2019 23:29, Gerlando Falauto wrote: > > IMHO, adding the ORDER BY clause to query 1) above (i.e. query 2) should > > ideally yield the exact same query plan. > > In the end adding an ORDER BY clause on the exact same columns of the > index > > used to traverse the table, should be easily recognizable. > > Knowing absolutely nothing about the internals though, I have no idea > > whether this particular use case has been overlooked, or it would just be > > unfeasible to handle it. > > > In SQL, when doing a SELECT, the order of the results is undetermined > (by definition). > > If you want/need to results to be ORDERed, you need to add 'ORDER BY'. > This will always show as an extra step in your QUERY PLAN. > > One can never know (for sure) if the output of this is in the correct > order: > > CREATE TABLE test(i primary key); > INSERT INTO test values(4); > INSERT INTO test values(2); > INSERT INTO test values(3); > INSERT INTO test values(1); > SELECT i FROM test; > > 4 > 2 > 3 > 1 > > To 'know' it is in the correct order one has to define an ORDER BY to > specify in which order the data should be returned > > SELECT i FROM test order by 2*i+i%2; > > 1 > 2 > 3 > 4 > > > > _______________________________________________ > 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
