Because your fields are backwards? x should come before _id (x is a row selector, _id is a grouping selector), and the y cannot be used to sort (obviously) but can be used to avoid the table lookup to feed the results into the temp b-tree sorter.
sqlite> CREATE TABLE foo (_id integer primary key, x, y); sqlite> CREATE INDEX i on foo(_id, x, y); sqlite> CREATE INDEX j on foo(x, _id, y); sqlite> CREATE INDEX k on foo(y, x, _id); sqlite> .eqp on sqlite> SELECT * FROM foo WHERE x=1 GROUP BY _id ORDER BY y; --EQP-- 0,0,0,SEARCH TABLE foo USING COVERING INDEX j (x=?) --EQP-- 0,0,0,USE TEMP B-TREE FOR ORDER BY sqlite> .eqp full sqlite> SELECT * FROM foo WHERE x=1 GROUP BY _id ORDER BY y; --EQP-- 0,0,0,SEARCH TABLE foo USING COVERING INDEX j (x=?) --EQP-- 0,0,0,USE TEMP B-TREE FOR ORDER BY addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 50 0 00 Start at 50 1 SorterOpen 1 5 0 k(1,B) 00 2 Noop 2 3 0 00 3 Integer 0 5 0 00 r[5]=0; clear abort flag 4 Integer 0 4 0 00 r[4]=0; indicate accumulator empty 5 Null 0 8 8 00 r[8..8]=NULL 6 Gosub 7 39 0 00 7 OpenRead 3 4 0 k(4,,,,) 02 root=4 iDb=0; j 8 Noop 0 0 0 00 Begin WHERE-loop0: foo 9 CursorHint 3 0 0 EQ(c0,1) 00 10 Integer 1 10 0 00 r[10]=1 11 SeekGE 3 27 10 1 00 key=r[10] 12 IdxGT 3 27 10 1 00 key=r[10] 13 Noop 0 0 0 00 Begin WHERE-core 14 IdxRowid 3 9 0 00 r[9]=rowid 15 Compare 8 9 1 k(1,B) 00 r[8] <-> r[9] 16 Jump 17 21 17 00 17 Move 9 8 1 00 r[8]=r[9] 18 Gosub 6 32 0 00 output one row 19 IfPos 5 41 0 00 if r[5]>0 then r[5]-=0, goto 41; check abort flag 20 Gosub 7 39 0 00 reset accumulator 21 IdxRowid 3 1 0 00 r[1]=rowid 22 Column 3 0 2 00 r[2]=foo.x 23 Column 3 2 3 00 r[3]=foo.y 24 Integer 1 4 0 00 r[4]=1; indicate data in accumulator 25 Noop 0 0 0 00 End WHERE-core 26 Next 3 12 0 00 27 Noop 0 0 0 00 End WHERE-loop0: foo 28 Gosub 6 32 0 00 output final row 29 Goto 0 41 0 00 30 Integer 1 5 0 00 r[5]=1; set abort flag 31 Return 6 0 0 00 32 IfPos 4 34 0 00 if r[4]>0 then r[4]-=0, goto 34; Groupby result generator entry point 33 Return 6 0 0 00 34 Copy 1 12 1 00 r[12..13]=r[1..2] 35 Copy 3 11 0 00 r[11]=r[3] 36 MakeRecord 11 3 15 00 r[15]=mkrec(r[11..13]) 37 SorterInsert 1 15 11 3 00 key=r[15] 38 Return 6 0 0 00 end groupby result generator 39 Null 0 1 3 00 r[1..3]=NULL 40 Return 7 0 0 00 41 OpenPseudo 4 16 5 00 5 columns in r[16] 42 SorterSort 1 49 0 00 43 SorterData 1 16 4 00 r[16]=data 44 Column 4 0 14 00 r[14]=y 45 Column 4 2 13 00 r[13]=x 46 Column 4 1 12 00 r[12]=_id 47 ResultRow 12 3 0 00 output=r[12..14] 48 SorterNext 1 43 0 00 49 Halt 0 0 0 00 50 Transaction 0 0 4 0 01 usesStmtJournal=0 51 Goto 0 1 0 00 s --- The fact that there's a Highway to Hell but only a Stairway to Heaven says 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 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 order by. Any clue why it doesn't or what I could do differently >to >get it to use an index for the selection, the grouping, and the >ordering? > >selectid = 0 > order = 0 > from = 0 > detail = SCAN TABLE foo > >selectid = 0 > order = 0 > from = 0 > detail = USE TEMP B-TREE FOR ORDER BY >_______________________________________________ >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
