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- >[email protected]] 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 <[email protected]> >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- >> >[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 >> >_______________________________________________ >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
