YES! Thank you!
Many thanks for the ".eqp full" tip also, that really explains a lot
(though I don't really understand any of it yet).
Have a great day!
Gerlando
On Mon, Jan 28, 2019 at 6:50 AM Keith Medcalf <kmedc...@dessus.com> wrote:
>
> Do you perhaps want this:
>
> select source1,
> source2,
> (
> select min(ts)
> from rolling
> where source1 = x.source1
> and source2 = x.source2
> )
> from (
> select distinct source1,
> source2
> from rolling
> ) as x;
>
> SQLite version 3.27.0 2019-01-28 00:42:06
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .timer on
> sqlite> CREATE TABLE `rolling` (
> ...> `source1` TEXT NOT NULL,
> ...> `source2` TEXT NOT NULL,
> ...> `ts` INTEGER NOT NULL,
> ...> `value` TEXT
> ...> );
> Run Time: real 0.002 user 0.000000 sys 0.000000
> sqlite>
> sqlite> CREATE INDEX `sources` ON `rolling` (
> ...> `source1`,
> ...> `source2`,
> ...> `ts`
> ...> );
> Run Time: real 0.001 user 0.000000 sys 0.000000
> sqlite>
> sqlite> INSERT INTO rolling
> ...> WITH RECURSIVE
> ...> src1( source1 ) AS ( VALUES("aaa") UNION ALL VALUES("bbb") ),
> ...> src2( source2 ) AS ( VALUES("X1") UNION ALL VALUES("X2")
> UNION ALL
> ...> VALUES("X3") UNION ALL VALUES("X4") ),
> ...> cnt( ts, value) AS (
> ...> VALUES( 0, "ZZZZ")
> ...> UNION ALL
> ...> SELECT ts+1, value FROM cnt LIMIT 1000000)
> ...>
> ...> select src1.source1, src2.source2, cnt.* from src1, src2, cnt;
> Run Time: real 8.920 user 8.843750 sys 0.078125
> sqlite>
> sqlite> analyze;
> Run Time: real 1.285 user 1.281250 sys 0.000000
> sqlite> .eqp full
> sqlite>
> sqlite> select source1,
> ...> source2,
> ...> (
> ...> select min(ts)
> ...> from rolling
> ...> where source1 = x.source1
> ...> and source2 = x.source2
> ...> )
> ...> from (
> ...> select distinct source1,
> ...> source2
> ...> from rolling
> ...> ) as x;
> QUERY PLAN
> |--CO-ROUTINE 2
> | `--SCAN TABLE rolling USING COVERING INDEX sources (~7864320 rows)
> |--SCAN SUBQUERY 2 AS x (~7864320 rows)
> `--CORRELATED SCALAR SUBQUERY 1
> `--SEARCH TABLE rolling USING COVERING INDEX sources (source1=? AND
> source2=?) (~983040 rows)
> addr opcode p1 p2 p3 p4 p5 comment
> ---- ------------- ---- ---- ---- ------------- -- -------------
> 0 Init 0 64 0 00 Start at 64
> 1 InitCoroutine 1 23 2 00 x
> 2 Null 1 4 0 08 r[4]=NULL
> 3 OpenRead 4 3 0 k(4,,,,) 00 root=3 iDb=0;
> sources
> 4 ColumnsUsed 4 0 0 3 00
> 5 Explain 5 0 0 SCAN TABLE rolling USING COVERING
> INDEX sources (~7864320 rows) 00
> 6 Noop 0 0 0 00 Begin
> WHERE-loop0: rolling
> 7 Rewind 4 21 2 0 00
> 8 Noop 0 0 0 00 Begin
> WHERE-core
> 9 Column 4 0 2 00
> r[2]=rolling.source1
> 10 Column 4 1 3 00
> r[3]=rolling.source2
> 11 Ne 2 13 4 (BINARY) 80 if
> r[4]!=r[2] goto 13
> 12 Eq 3 20 5 (BINARY) 80 if
> r[5]==r[3] goto 20
> 13 Copy 2 4 1 00
> r[4..5]=r[2..3]
> 14 Yield 1 0 0 00
> 15 Noop 0 0 0 00 End
> WHERE-core
> 16 Column 4 0 6 00 r[6]=
> 17 Column 4 1 7 00 r[7]=
> 18 SeekGT 4 21 6 2 00 key=r[6..7]
> 19 Goto 1 8 0 00
> 20 Next 4 8 0 01
> 21 Noop 0 0 0 00 End WHERE-loop0:
> rolling
> 22 EndCoroutine 1 0 0 00
> 23 Explain 23 0 0 SCAN SUBQUERY 2 AS x (~7864320
> rows) 00
> 24 Noop 0 0 0 00 Begin
> WHERE-loop0: x
> 25 InitCoroutine 1 0 2 00
> 26 Yield 1 62 0 00 next row of x
> 27 Noop 0 0 0 00 Begin
> WHERE-core
> 28 Copy 2 9 0 00 r[9]=r[2];
> x.source1
> 29 Copy 3 10 0 00 r[10]=r[3];
> x.source2
> 30 Null 0 12 12 00
> r[12..12]=NULL; Init subquery result
> 31 Integer 1 13 0 00 r[13]=1; LIMIT
> counter
> 32 Null 0 14 15 00 r[14..15]=NULL
> 33 OpenRead 5 3 0 k(4,,,,) 00 root=3 iDb=0;
> sources
> 34 ColumnsUsed 5 0 0 7 00
> 35 Explain 35 0 0 SEARCH TABLE rolling USING
> COVERING INDEX sources (source1=? AND source2=?) (~983040 rows) 00
> 36 Noop 0 0 0 00 Begin
> WHERE-loop0: rolling
> 37 Copy 2 16 0 00 r[16]=r[2];
> x.source1
> 38 Copy 3 17 0 00 r[17]=r[3];
> x.source2
> 39 CursorHint 5 0 0 AND(EQ(c0,r[16]),EQ(c1,r[17])) 00
> 40 Copy 2 18 0 00 r[18]=r[2];
> x.source1
> 41 IsNull 18 54 0 00 if r[18]==NULL
> goto 54
> 42 Copy 3 19 0 00 r[19]=r[3];
> x.source2
> 43 IsNull 19 54 0 00 if r[19]==NULL
> goto 54
> 44 Null 0 20 0 00 r[20]=NULL
> 45 SeekGT 5 54 18 3 00 key=r[18..20]
> 46 IdxGT 5 54 18 2 00 key=r[18..19]
> 47 Noop 0 0 0 00 Begin
> WHERE-core
> 48 Column 5 2 21 00
> r[21]=rolling.ts
> 49 CollSeq 0 0 0 (BINARY) 00
> 50 AggStep 0 21 14 min(1) 01 accum=r[14]
> step(r[21])
> 51 Goto 0 55 0 00 min() by
> index
> 52 Noop 0 0 0 00 End
> WHERE-core
> 53 Next 5 46 0 00
> 54 Noop 0 0 0 00 End
> WHERE-loop0: rolling
> 55 AggFinal 14 1 0 min(1) 00 accum=r[14] N=1
> 56 Copy 14 12 0 00 r[12]=r[14]
> 57 DecrJumpZero 13 58 0 00 if
> (--r[13])==0 goto 58
> 58 Copy 12 11 0 00 r[11]=r[12]
> 59 ResultRow 9 3 0 00 output=r[9..11]
> 60 Noop 0 0 0 00 End WHERE-core
> 61 Goto 0 26 0 00
> 62 Noop 0 0 0 00 End WHERE-loop0:
> x
> 63 Halt 0 0 0 00
> 64 Transaction 0 0 3 0 01 usesStmtJournal=0
> 65 Goto 0 1 0 00
> aaa|X1|0
> aaa|X2|0
> aaa|X3|0
> aaa|X4|0
> bbb|X1|0
> bbb|X2|0
> bbb|X3|0
> bbb|X4|0
> Run Time: real 0.134 user 0.000000 sys 0.000000
> sqlite>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> _______________________________________________
> 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
