On Tue, 5 Feb 2019 at 22:46, Simon Slavin <[email protected]> wrote:
> 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. > This seems like a reasonable hypothesis, and explains one of Gerlando's observations (sqlite _did_ decide to use an index on `ts` in a different version of the DB). However, the EXPLAIN QUERY PLAN output demonstrates that it _is_ using the `sources` index when that's the only one available: QUERY PLAN |--SEARCH TABLE rolling USING INDEX sources (ANY(source1) AND ANY(source2) AND ts>? AND ts<?) `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY (sorry for omitting this detail; this all comes from the start of the thread in Gerlando's 3rd email) What makes the TEMP B-TREE necessary, when sqlite is already using the perfect index to satisfy the ORDER BY? -Rowan _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
