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
> 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.
> sqlite-users mailing list
sqlite-users mailing list