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
