On 5 Feb 2019, at 8:59am, Rowan Worth <[email protected]> wrote: > 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?
I suspect that, given the data in the table, the index supplied is not optimal for selecting the correct rows from the table. SQLite may have decided that it needs to select on the contents of ts first, then source1. 1) Put some typical data in the table. The table should have an appropriate number of rows, and appropriate values in each field. 2) Add an index for (ts, source1, source2). 3) Add an index for (source1, ts, source2, value). 4) Add an index for (ts, source1, source2, value). 5) Run ANALYZE 6) Use EXPLAIN QUERY PLAN on the above SELECT to find which index SQLite chose. You now know what SQLite thinks is the best index for the query. You can delete the other indexes and run VACUUM. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
