subject:"\[sqlite\] question about covering index"

Re: [sqlite] question about covering index

2018-02-06 Thread Simon Slavin

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 the lowest surname, not the lowest 
firstname.  This is why your covering index doesn't make sense.

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

Re: [sqlite] question about covering index

2018-02-06 Thread Keith Medcalf


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 p1p2p3p4 p5  comment
>> >  -        -  --  
>---
>> >--
>> >0 Init   0 50000  Start at
>50
>> >1 SorterOpen 1 5 0 k(1,B) 00
>> >2 Noop   2 3 000
>> >3 Integer0 5 000  r[5]=0;
>> >clear abort flag
>> >4 Integer0 4 000  r[4]=0;
>> >indicate accumulator empty
>> >5 Null   0 8 800
>> >r[8..8]=NULL
>> >6 Gosub  7 39000
>> >7 OpenRead   3 4 0 k(4)   02  root=4
>> >iDb=0; j
>> >8 Noop   0 0 000  Begin
>> >WHERE-loop0: foo
>> >9 CursorHint 3 0 0 EQ(c0,1)   00
>> >10Integer1 10000  r[10]=1
>> >11SeekGE 3 27101  00
>key=r[10]
>> >12  IdxGT  3 27101  00
>key=r[10]
>> >13  Noop   0 0 000  Begin
>> >WHERE-core
>> >14  IdxRowid   3 9 000
>> >r[9]=rowid
>> >15  Compare8 9 1 k(1,B) 00  r[8]
><->
>> >r[9]
>> >16  Jump   172117   00
>> >17  Move   9 8 100
>r[8]=r[9]
>> >18  Gosub  6 32000  output
>> >one row
>> >19  IfPos  5 41000  if
>r[5]>0
>> >then r[5]-=0, goto 41; check abort flag
&

Re: [sqlite] question about covering index

2018-02-06 Thread Mark Wagner

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 p1p2p3p4 p5  comment
> >  -        -  --  ---
> >--
> >0 Init   0 50000  Start at 50
> >1 SorterOpen 1 5 0 k(1,B) 00
> >2 Noop   2 3 000
> >3 Integer0 5 000  r[5]=0;
> >clear abort flag
> >4 Integer0 4 000  r[4]=0;
> >indicate accumulator empty
> >5 Null   0 8 800
> >r[8..8]=NULL
> >6 Gosub  7 39000
> >7 OpenRead   3 4 0 k(4)   02  root=4
> >iDb=0; j
> >8 Noop   0 0 000  Begin
> >WHERE-loop0: foo
> >9 CursorHint 3 0 0 EQ(c0,1)   00
> >10Integer1 10000  r[10]=1
> >11SeekGE 3 27101  00  key=r[10]
> >12  IdxGT  3 27101  00  key=r[10]
> >13  Noop   0 0 000  Begin
> >WHERE-core
> >14  IdxRowid   3 9 000
> >r[9]=rowid
> >15  Compare8 9 1 k(1,B) 00  r[8] <->
> >r[9]
> >16  Jump   172117   00
> >17  Move   9 8 100  r[8]=r[9]
> >18  Gosub  6 32000  output
> >one row
> >19  IfPos  5 41000  if r[5]>0
> >then r[5]-=0, goto 41; check abort flag
> >20  Gosub  7 39000  reset
> >accumulator
> >21  IdxRowid   3 1 000
> >r[1]=rowid
> >22  Column 3 0 200
> >r[2]=foo.x
> >23  Column 3 2 300
> >r[3]=foo.y
> >24  Integer1 4 000  r[4]=1;
> >indicate data in accumulator
> >25  Noop   0 0 000  End
> >WHERE-core
> >26Next   3 12000
> >27Noop   0 0 000  End WHERE-
> >loop0: foo
> >28Gosub  6 32000  output
> >final row
> >29Goto   0 41000
> >30Integer1 5 000  r[5]=1; set
> >abort flag
&

Re: [sqlite] question about covering index

2018-02-06 Thread Keith Medcalf


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 p1p2p3p4 p5  comment
>  -        -  --  ---
>--
>0 Init   0 50000  Start at 50
>1 SorterOpen 1 5 0 k(1,B) 00
>2 Noop   2 3 000
>3 Integer0 5 000  r[5]=0;
>clear abort flag
>4 Integer0 4 000  r[4]=0;
>indicate accumulator empty
>5 Null   0 8 800
>r[8..8]=NULL
>6 Gosub  7 39000
>7 OpenRead   3 4 0 k(4)   02  root=4
>iDb=0; j
>8 Noop   0 0 000  Begin
>WHERE-loop0: foo
>9 CursorHint 3 0 0 EQ(c0,1)   00
>10Integer1 10000  r[10]=1
>11SeekGE 3 27101  00  key=r[10]
>12  IdxGT  3 27101  00  key=r[10]
>13  Noop   0 0 000  Begin
>WHERE-core
>14  IdxRowid   3 9 000
>r[9]=rowid
>15  Compare8 9 1 k(1,B) 00  r[8] <->
>r[9]
>16  Jump   172117   00
>17  Move   9 8 100  r[8]=r[9]
>18  Gosub  6 32000  output
>one row
>19  IfPos  5 41000  if r[5]>0
>then r[5]-=0, goto 41; check abort flag
>20  Gosub  7 39000  reset
>accumulator
>21  IdxRowid   3 1 000
>r[1]=rowid
>22  Column 3 0 200
>r[2]=foo.x
>23  Column 3 2 300
>r[3]=foo.y
>24  Integer1 4 000  r[4]=1;
>indicate data in accumulator
>25  Noop   0 0 000  End
>WHERE-core
>26Next   3 12000
>27Noop   0 0 000  End WHERE-
>loop0: foo
>28Gosub  6 32000  output
>final row
>29Goto   0 41000
>30Integer1 5 000  r[5]=1; set
>abort flag
>31Return 6 0 000
>32IfPos  4 34000  if r[4]>0
>then r[4]-=0, goto 34; Groupby result generator entry point
>33Return 6 0 000
>34Copy   1 12100
>r[12..13]=r[1..2]
>35Copy   3 11000  r[11]=r[3]
>36MakeRecord 113 15   00
>r[15]=mkrec(r[11..13])
>37SorterInsert   1 15113  00  key=r[15]
>38Return 6 0 000  end groupb

Re: [sqlite] question about covering index

2018-02-06 Thread Keith Medcalf

 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

Re: [sqlite] question about covering index

2018-02-06 Thread Mark Wagner

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 Slavin  wrote:

> 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 primary key ?  Primary key values must be, by
> definition, unique.  Grouping by a unique value means every group has one
> entry.
>
> There's a similar problem with the index you created.  Since the primary
> key is first, there's no point in having the x and y in the index.
> Therefore there's no point in having the index since it just duplicates the
> primary key index for the table.
>
> I suspect that SQLite is acting weird because you fed it with weird things.
>
> Simon.
> ___
> 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

Re: [sqlite] question about covering index

2018-02-06 Thread Simon Slavin

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 primary key ?  Primary key values must be, by 
definition, unique.  Grouping by a unique value means every group has one entry.

There's a similar problem with the index you created.  Since the primary key is 
first, there's no point in having the x and y in the index.  Therefore there's 
no point in having the index since it just duplicates the primary key index for 
the table.

I suspect that SQLite is acting weird because you fed it with weird things.

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

[sqlite] question about covering index

2018-02-06 Thread Mark Wagner

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

Re: [sqlite] question about covering index

Re: [sqlite] question about covering index

Re: [sqlite] question about covering index

Re: [sqlite] question about covering index

Re: [sqlite] question about covering index

Re: [sqlite] question about covering index

Re: [sqlite] question about covering index

[sqlite] question about covering index

8 matches

Site Navigation

Mail list logo

Footer information