Re: [sqlite] question about covering index

On 7 Feb 2018, at 1:31am, Mark Wagner wrote: > Wow, I had no idea that the order of the columns in the index effects how > they're used. Must. Study. More. Just like a phone directory. If the order is (surname, firstname) then the first name in the directory is the one with

Re: [sqlite] question about covering index

a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Mark Wagner >Sent: Tuesday, 6 February, 2018 18:32 >To: SQLite mailing list >Subject: Re: [sqlite] question about covering i

Re: [sqlite] question about covering index

ers- > >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf > >Sent: Tuesday, 6 February, 2018 18:07 > >To: SQLite mailing list > >Subject: Re: [sqlite] question about covering index > > > > > >Because your fields are backwards? > > > >x

Re: [sqlite] question about covering index

ge- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Tuesday, 6 February, 2018 18:07 >To: SQLite mailing list >Subject: Re: [sqlite] question about covering index > > >Because your fields are backwards? > >x

Re: [sqlite] question about covering index

ays a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Mark Wagner >Sent: Tuesday, 6 February, 2018 17:44 >To: SQLite mailing list >Subject: [sqlite] question about covering in

Re: [sqlite] question about covering index

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

Re: [sqlite] question about covering index

On 7 Feb 2018, at 12:43am, Mark Wagner 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

[sqlite] question about covering index

Given the following schema: 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; I would have expected it (hoped?) that it would use the covering index for the