Hi Luuk, It says: > > SQLite *attempts* to use an index to satisfy the ORDER BY clause of a > query when possible > > > To be (abolutely!) SURE results are in the correct order, you need an > ORDER BY. >
No questioning about that. ORDER BY *must* be there in order to get the results correctly sorted. What I'm saying is it shouldn't add any extra overhead, which apparently does in my case. Notice how ORDER BY does not add any in all the trivial cases: sqlite> explain query plan select * from rolling order by source1,source2,ts; QUERY PLAN `--SCAN TABLE rolling USING INDEX sources sqlite> explain query plan select * from rolling order by source1,source2; QUERY PLAN `--SCAN TABLE rolling USING INDEX sources sqlite> explain query plan select * from rolling order by source1; QUERY PLAN `--SCAN TABLE rolling USING INDEX sources sqlite> explain query plan select source1,source2,ts from rolling order by source1,source2,ts; QUERY PLAN `--SCAN TABLE rolling USING COVERING INDEX sources sqlite> explain query plan select source1,source2,ts from rolling order by source1,source2; QUERY PLAN `--SCAN TABLE rolling USING COVERING INDEX sources sqlite> explain query plan select source1,source2,ts from rolling order by source1; QUERY PLAN `--SCAN TABLE rolling USING COVERING INDEX sources It's just that when things get a bit more complicated, I start getting surprising results (for my use case -- which, I admit, is unusual at least). Any suggestion welcome. Thanks, Gerlando _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
