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- >[email protected]] 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- >>[email protected]] 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 >>[email protected] >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > >_______________________________________________ >sqlite-users mailing list >[email protected] >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
