2017-08-24 11:29 GMT+02:00 Clemens Ladisch <[email protected]>: > Cecil Westerhof wrote: > > I always use an ORDER BY after a GROUP BY. > > Without an ORDER BY, there is no guarantee that the result has any > specific order (in SQLite and in any other SQL database). >
That is what I thought, but you never know. So I just wanted to check. > > For example: > > SELECT used > > , COUNT(used) > > FROM usedProverbs > > GROUP BY used > > ORDER BY used > > > > But when I leave the ORDER BY out in this case, the result is the same, > but > > it looks like it is a bit faster. > > Are you sure? How does the output of EXPLAIN QUERY PLAN look like? > No, I am not sure. I executed both a few times in the database browser. Both have widely varying used times. But at first glance it looked that without ORDER BY is about 2 times faster. But it is only at first glance, so certainly not something concrete. The explains. I do not really see a difference. There is only an offset difference. I also do not see a sort. (But this is the first time I look at this kind of code.) EXPLAIN SELECT used, COUNT(used) FROM usedProverbs GROUP BY used ORDER BY used; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 39 0 00 Start at 39 1 Noop 2 4 0 00 2 Noop 3 1 0 00 3 Integer 0 4 0 00 r[4]=0; clear abort flag 4 Integer 0 3 0 00 r[3]=0; indicate accumulator empty 5 Null 0 7 7 00 r[7..7]=NULL 6 Gosub 6 36 0 00 7 OpenRead 1 290 0 3 00 root=290 iDb=0; proverbs 8 OpenRead 4 6 0 k(2,,) 00 root=6 iDb=0; proverbs_used_idx 9 Rewind 4 26 9 0 00 10 Seek 4 0 1 00 Move 1 to 4.rowid 11 Column 4 0 9 notUsed 00 r[9]=proverbs.used 12 Cast 9 68 0 00 affinity(r[9]) 13 Eq 10 25 9 (BINARY) 54 if r[9]==r[10] goto 25 14 Column 4 0 8 notUsed 00 r[8]=proverbs.used 15 Compare 7 8 1 k(1,B) 00 r[7] <-> r[8] 16 Jump 17 21 17 00 17 Move 8 7 1 00 r[7]=r[8] 18 Gosub 5 30 0 00 output one row 19 IfPos 4 38 0 00 if r[4]>0 then r[4]-=0, goto 38; check abort flag 20 Gosub 6 36 0 00 reset accumulator 21 Column 4 0 9 notUsed 00 r[9]=proverbs.used 22 AggStep0 0 9 2 count(1) 01 accum=r[2] step(r[9]) 23 Column 4 0 1 notUsed 00 r[1]=proverbs.used 24 Integer 1 3 0 00 r[3]=1; indicate data in accumulator 25 Next 4 10 0 01 26 Gosub 5 30 0 00 output final row 27 Goto 0 38 0 00 28 Integer 1 4 0 00 r[4]=1; set abort flag 29 Return 5 0 0 00 30 IfPos 3 32 0 00 if r[3]>0 then r[3]-=0, goto 32; Groupby result generator entry point 31 Return 5 0 0 00 32 AggFinal 2 1 0 count(1) 00 accum=r[2] N=1 33 Copy 1 11 1 00 r[11..12]=r[1..2] 34 ResultRow 11 2 0 00 output=r[11..12] 35 Return 5 0 0 00 end groupby result generator 36 Null 0 1 2 00 r[1..2]=NULL 37 Return 6 0 0 00 38 Halt 0 0 0 00 39 Transaction 0 0 65 0 01 usesStmtJournal=0 40 Integer 0 10 0 00 r[10]=0 41 Goto 0 1 0 00 EXPLAIN SELECT used, COUNT(used) FROM usedProverbs GROUP BY used; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 38 0 00 Start at 38 1 Noop 2 1 0 00 2 Integer 0 4 0 00 r[4]=0; clear abort flag 3 Integer 0 3 0 00 r[3]=0; indicate accumulator empty 4 Null 0 7 7 00 r[7..7]=NULL 5 Gosub 6 35 0 00 6 OpenRead 1 290 0 3 00 root=290 iDb=0; proverbs 7 OpenRead 3 6 0 k(2,,) 00 root=6 iDb=0; proverbs_used_idx 8 Rewind 3 25 9 0 00 9 Seek 3 0 1 00 Move 1 to 3.rowid 10 Column 3 0 9 notUsed 00 r[9]=proverbs.used 11 Cast 9 68 0 00 affinity(r[9]) 12 Eq 10 24 9 (BINARY) 54 if r[9]==r[10] goto 24 13 Column 3 0 8 notUsed 00 r[8]=proverbs.used 14 Compare 7 8 1 k(1,B) 00 r[7] <-> r[8] 15 Jump 16 20 16 00 16 Move 8 7 1 00 r[7]=r[8] 17 Gosub 5 29 0 00 output one row 18 IfPos 4 37 0 00 if r[4]>0 then r[4]-=0, goto 37; check abort flag 19 Gosub 6 35 0 00 reset accumulator 20 Column 3 0 9 notUsed 00 r[9]=proverbs.used 21 AggStep0 0 9 2 count(1) 01 accum=r[2] step(r[9]) 22 Column 3 0 1 notUsed 00 r[1]=proverbs.used 23 Integer 1 3 0 00 r[3]=1; indicate data in accumulator 24 Next 3 9 0 01 25 Gosub 5 29 0 00 output final row 26 Goto 0 37 0 00 27 Integer 1 4 0 00 r[4]=1; set abort flag 28 Return 5 0 0 00 29 IfPos 3 31 0 00 if r[3]>0 then r[3]-=0, goto 31; Groupby result generator entry point 30 Return 5 0 0 00 31 AggFinal 2 1 0 count(1) 00 accum=r[2] N=1 32 Copy 1 11 1 00 r[11..12]=r[1..2] 33 ResultRow 11 2 0 00 output=r[11..12] 34 Return 5 0 0 00 end groupby result generator 35 Null 0 1 2 00 r[1..2]=NULL 36 Return 6 0 0 00 37 Halt 0 0 0 00 38 Transaction 0 0 65 0 01 usesStmtJournal=0 39 Integer 0 10 0 00 r[10]=0 40 Goto 0 1 0 00 -- Cecil Westerhof _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
