OK, I oversimplified trying to make it easier. The real query has a join so I'm aggregating some of the columns. But this test case seemed to show the issue. I could show something closer to what I'm really doing if that explanation isn't sufficient.
On Tue, Feb 6, 2018 at 4:48 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 7 Feb 2018, at 12:43am, Mark Wagner <m...@google.com> wrote: > > > CREATE TABLE foo (_id integer primary key, x, y); > > CREATE INDEX i on foo(_id, x, y); > > > > And the following query > > > > sqlite> EXPLAIN QUERY PLAN SELECT * FROM foo WHERE x=1 GROUP BY _id ORDER > > BY y; > > Why are you grouping on the primary key ? Primary key values must be, by > definition, unique. Grouping by a unique value means every group has one > entry. > > There's a similar problem with the index you created. Since the primary > key is first, there's no point in having the x and y in the index. > Therefore there's no point in having the index since it just duplicates the > primary key index for the table. > > I suspect that SQLite is acting weird because you fed it with weird things. > > Simon. > _______________________________________________ > 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
