pastebin schema pasted by wasamasa on Thu Oct 29 13:21:15 2020
create table pastes(hash text, author text, title text, time float, paste text);
pastebin fill script pasted by wasamasa on Thu Oct 29 13:21:33 2020
(import scheme) (import (chicken base)) (import (chicken random)) (import (chicken process-context)) (import simple-sha1) (import sql-de-lite) (define (main db-path) (call-with-database db-path (lambda (db) (with-transaction db (lambda () (let loop ((i 0)) (when (< i 15000) (let ((hash (string->sha1sum (number->string i))) (author "wasa") (title "test") (paste "123") (pastes (add1 (pseudo-random-integer 3)))) (let loop ((j 0)) (when (< j pastes) (exec (sql db "INSERT INTO pastes VALUES(?, ?, ?, ?, ?)") hash author title (+ i j) paste) (loop (add1 j)))) (loop (+ i pastes)))))))))) (apply main (command-line-arguments))
explain (query plan) added by wasamasa on Thu Oct 29 13:26:24 2020
SQLite version 3.33.0 2020-08-14 13:23:32 Enter ".help" for usage hints. sqlite> explain select * from pastes p where time = (select min(time) from pastes p2 where p2.hash=p.hash) order by time desc limit 10; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 57 0 0 Start at 57 1 OpenEphemeral 2 7 0 k(1,-B) 0 nColumn=7 2 Integer 10 1 0 0 r[1]=10; LIMIT counter 3 OpenRead 0 2 0 5 0 root=2 iDb=0; pastes 4 Rewind 0 48 0 0 5 Column 0 3 2 0 r[2]=pastes.time 6 RealAffinity 2 0 0 0 7 Null 0 4 4 0 r[4..4]=NULL; Init subquery result 8 Integer 1 5 0 0 r[5]=1; LIMIT counter 9 Null 0 6 7 0 r[6..7]=NULL 10 OpenRead 1 2 0 4 0 root=2 iDb=0; pastes 11 Once 0 21 0 0 12 OpenAutoindex 3 3 0 k(3,B,,) 0 nColumn=3; for pastes 13 Rewind 1 21 0 0 14 Column 1 0 9 0 r[9]=pastes.hash 15 Column 1 3 10 0 r[10]=pastes.time 16 Noop 10 0 0 0 17 Rowid 1 11 0 0 r[11]=rowid 18 MakeRecord 9 3 8 0 r[8]=mkrec(r[9..11]) 19 IdxInsert 3 8 0 16 key=r[8] 20 Next 1 14 0 3 21 Column 0 0 12 0 r[12]=pastes.hash 22 IsNull 12 30 0 0 if r[12]==NULL goto 30 23 SeekGE 3 30 12 1 0 key=r[12] 24 IdxGT 3 30 12 1 0 key=r[12] 25 Column 3 1 8 0 r[8]=pastes.time 26 RealAffinity 8 0 0 0 27 CollSeq 0 0 0 BINARY-8 0 28 AggStep 0 8 6 min(1) 1 accum=r[6] step(r[8]) 29 Next 3 24 0 0 30 AggFinal 6 1 0 min(1) 0 accum=r[6] N=1 31 Copy 6 4 0 0 r[4]=r[6] 32 DecrJumpZero 5 33 0 0 if (--r[5])==0 goto 33 33 Ne 4 47 2 BINARY-8 85 if r[2]!=r[4] goto 47 34 Column 0 3 13 0 r[13]=pastes.time 35 RealAffinity 13 0 0 0 36 Sequence 2 14 0 0 r[14]=cursor[2].ctr++ 37 IfNotZero 1 41 0 0 if r[1]!=0 then r[1]--, goto 41 38 Last 2 0 0 0 39 IdxLE 2 47 13 1 0 key=r[13] 40 Delete 2 0 0 0 41 Column 0 0 15 0 r[15]=pastes.hash 42 Column 0 1 16 0 r[16]=pastes.author 43 Column 0 2 17 0 r[17]=pastes.title 44 Column 0 4 18 0 r[18]=pastes.paste 45 MakeRecord 13 6 20 0 r[20]=mkrec(r[13..18]) 46 IdxInsert 2 20 13 6 0 key=r[20] 47 Next 0 5 0 1 48 Sort 2 56 0 0 49 Column 2 5 19 0 r[19]=paste 50 Column 2 0 18 0 r[18]=time 51 Column 2 4 17 0 r[17]=title 52 Column 2 3 16 0 r[16]=author 53 Column 2 2 15 0 r[15]=hash 54 ResultRow 15 5 0 0 output=r[15..19] 55 Next 2 49 0 0 56 Halt 0 0 0 0 57 Transaction 0 0 2 0 1 usesStmtJournal=0 58 Goto 0 1 0 0 sqlite> explain query plan select * from pastes p where time = (select min(time) from pastes p2 where p2.hash=p.hash) order by time desc limit 10; QUERY PLAN |--SCAN TABLE pastes AS p |--CORRELATED SCALAR SUBQUERY 1 | `--SEARCH TABLE pastes AS p2 USING AUTOMATIC COVERING INDEX (hash=?) `--USE TEMP B-TREE FOR ORDER BY sqlite> explain select hash, author, title, min(time), paste from pastes group by hash order by time desc limit 10; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 74 0 0 Start at 74 1 OpenEphemeral 1 7 0 k(1,-B) 0 nColumn=7 2 Integer 10 1 0 0 r[1]=10; LIMIT counter 3 Integer 20 2 0 0 r[2]=20 4 MustBeInt 2 0 0 0 OFFSET counter 5 OffsetLimit 1 3 2 0 if r[1]>0 then r[3]=r[1]+max(0,r[2]) else r[3]=(-1); LIMIT+OFFSET 6 SorterOpen 2 5 0 k(1,-B) 0 7 Integer 0 11 0 0 r[11]=0; clear abort flag 8 Null 0 14 14 0 r[14..14]=NULL 9 Gosub 13 61 0 0 10 OpenRead 0 2 0 5 0 root=2 iDb=0; pastes 11 Rewind 0 21 0 0 12 Column 0 0 16 0 r[16]=pastes.hash 13 Column 0 1 17 0 r[17]=pastes.author 14 Column 0 2 18 0 r[18]=pastes.title 15 Column 0 4 19 0 r[19]=pastes.paste 16 Column 0 3 20 0 r[20]=pastes.time 17 RealAffinity 20 0 0 0 18 MakeRecord 16 5 21 0 r[21]=mkrec(r[16..20]) 19 SorterInsert 2 21 0 0 key=r[21] 20 Next 0 12 0 1 21 OpenPseudo 3 21 5 0 5 columns in r[21] 22 SorterSort 2 64 0 0 GROUP BY sort 23 SorterData 2 21 3 0 r[21]=data 24 Column 3 0 15 0 r[15]= 25 Compare 14 15 1 k(1,-B) 0 r[14] <-> r[15] 26 Jump 27 31 27 0 27 Move 15 14 1 0 r[14]=r[15] 28 Gosub 12 48 0 0 output one row 29 IfPos 11 64 0 0 if r[11]>0 then r[11]-=0, goto 64; check abort flag 30 Gosub 13 61 0 0 reset accumulator 31 Column 3 4 22 0 r[22]=pastes.time 32 RealAffinity 22 0 0 0 33 CollSeq 23 0 0 BINARY-8 0 34 AggStep 0 22 7 min(1) 1 accum=r[7] step(r[22]) 35 If 23 42 0 0 36 Column 3 0 4 0 r[4]=pastes.hash 37 Column 3 1 5 0 r[5]=pastes.author 38 Column 3 2 6 0 r[6]=pastes.title 39 Column 3 3 8 0 r[8]=pastes.paste 40 Column 3 4 9 0 r[9]=pastes.time 41 RealAffinity 9 0 0 0 42 Integer 1 10 0 0 r[10]=1; indicate data in accumulator 43 SorterNext 2 23 0 0 44 Gosub 12 48 0 0 output final row 45 Goto 0 64 0 0 46 Integer 1 11 0 0 r[11]=1; set abort flag 47 Return 12 0 0 0 48 IfPos 10 50 0 0 if r[10]>0 then r[10]-=0, goto 50; Groupby result generator entry point 49 Return 12 0 0 0 50 AggFinal 7 1 0 min(1) 0 accum=r[7] N=1 51 Copy 9 24 0 0 r[24]=r[9] 52 Sequence 1 25 0 0 r[25]=cursor[1].ctr++ 53 IfNotZero 3 57 0 0 if r[3]!=0 then r[3]--, goto 57 54 Last 1 0 0 0 55 IdxLE 1 60 24 1 0 key=r[24] 56 Delete 1 0 0 0 57 Copy 4 26 4 0 r[26..30]=r[4..8] 58 MakeRecord 24 7 31 0 r[31]=mkrec(r[24..30]) 59 IdxInsert 1 31 24 7 0 key=r[31] 60 Return 12 0 0 0 end groupby result generator 61 Null 0 4 9 0 r[4..9]=NULL 62 Integer 0 10 0 0 r[10]=0; indicate accumulator empty 63 Return 13 0 0 0 64 Sort 1 73 0 0 65 IfPos 2 72 1 0 if r[2]>0 then r[2]-=1, goto 72; OFFSET 66 Column 1 6 30 0 r[30]=paste 67 Column 1 5 29 0 r[29]=min(time) 68 Column 1 4 28 0 r[28]=title 69 Column 1 3 27 0 r[27]=author 70 Column 1 2 26 0 r[26]=hash 71 ResultRow 26 5 0 0 output=r[26..30] 72 Next 1 65 0 0 73 Halt 0 0 0 0 74 Transaction 0 0 2 0 1 usesStmtJournal=0 75 Goto 0 1 0 0 sqlite> explain query plan select hash, author, title, min(time), paste from pastes group by hash order by time desc limit 10; QUERY PLAN |--SCAN TABLE pastes |--USE TEMP B-TREE FOR GROUP BY `--USE TEMP B-TREE FOR ORDER BY sqlite>