Note that if the _id column were not UNIQUE, then the SKIP-SCAN optimization 
might be used with index i if and only if you had (a) done an analyze and (b) 
the optimizer thought it might be worthwhile to do so.


---
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 18:32
>To: SQLite mailing list
>Subject: Re: [sqlite] question about covering index
>
>Wow, I had no idea that the order of the columns in the index effects
>how
>they're used.  Must. Study. More.
>
>On Tue, Feb 6, 2018 at 5:15 PM, Keith Medcalf <kmedc...@dessus.com>
>wrote:
>
>>
>> That said, however, the performance increase will be proportional
>to the
>> number of x values that are selected vs the number of rows in the
>table.
>> Unless the table is many orders of magnitude larger than the number
>of
>> similar x values you are searching for, the table scan will likely
>be
>> faster.  Of course, you will also "pay" the extra index maintenance
>and
>> storage fee's, which may or may not outweigh the increase
>conferred.
>>
>> ---
>> 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 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 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
>>
>>
>>
>> _______________________________________________
>> 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



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to