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

Reply via email to