Hick Gunter wrote: > You are in error on number 4. > > SQLite reads your CFL table once and performs the aggregation in > a temporary table that holds 1 entry per "contract" i.e. about > 1000 rows of max 16 byte records which I estimate to using less than > 64k. > > For each record read, SQLite will update or insert the matching row in > the temporary table.
sqlite> CREATE VIRTUAL TABLE t USING fts3(x,y); sqlite> EXPLAIN SELECT x, SUM(y) FROM t GROUP BY x; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 1 SorterOpen 1 3 0 keyinfo(1,BINARY) 00 nColumn=3 ... 7 VOpen 0 0 0 vtab:82F090:7F0C37EF47A0 00 8 Noop 0 0 0 00 Begin Join Loop 0 9 Integer 0 10 0 00 r[10]=0 10 Integer 0 11 0 00 r[11]=0 11 VFilter 0 18 10 00 i0an=r[10] z0an='' 12 VColumn 0 0 12 00 r[12]=vcolumn(0); t.x 13 Sequence 1 13 0 00 r[13]=rowid 14 VColumn 0 1 14 00 r[14]=vcolumn(1); t.y 15 MakeRecord 12 3 15 00 r[15]=mkrec(r[12..14]) 16 SorterInsert 1 15 0 00 17 VNext 0 12 0 00 ... This copies all records into the temporary table without yet aggregating them. Regards, Clemens _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
