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

Reply via email to