才一个400多K的可执行文件。做7个表笛卡尔积,共1千万行,不到1秒。

sqlite> select count(*) from
   ...> (select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10)a,
   ...> (select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10)b,
   ...> (select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10)c,
   ...> (select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10)d,
   ...> (select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10)e,
   ...> (select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10)f,
   ...> (select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10)g;
10000000
CPU Time: user 0.687500 sys 0.000000

如果count(*)可能用了取巧的办法,再做加法的求和,应该没什么可取巧的了。这回用了5秒,但6个表的列相加,比起计数用时大几倍可以理解。

sqlite> select sum(a.x+b.x+c.x+d.x+e.x+f.x) from
   ...> (select 1 x union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
union select 10)a,
   ...> (select 1 x union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
union select 10)b,
   ...> (select 1 x union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
union select 10)c,
   ...> (select 1 x union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
union select 10)d,
   ...> (select 1 x union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
union select 10)e,
   ...> (select 1 x union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
union select 10)f,
   ...> (select 1 x union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
union select 10)g;
330000000
CPU Time: user 5.359375 sys 0.000000

把1百万行查询结果写入物理表也很快,但建立索引比较慢。

sqlite> create table t1m (x int);
CPU Time: user 0.000000 sys 0.000000
sqlite> insert into t1m select a.x+b.x+c.x+d.x+e.x+f.x from
   ...> (select 1 x union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
union select 10)a,
   ...> (select 1 x union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
union select 10)b,
   ...> (select 1 x union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
union select 10)c,
   ...> (select 1 x union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
union select 10)d,
   ...> (select 1 x union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
union select 10)e,
   ...> (select 1 x union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
union select 10)f;
CPU Time: user 1.062500 sys 0.046875
sqlite> select count(*) from t1m;
1000000
CPU Time: user 0.015625 sys 0.015625
sqlite> create index t1m_x on t1m(x);
CPU Time: user 3.390625 sys 0.218750
sqlite> select count(*) from t1m where x=6;
1
CPU Time: user 0.000000 sys 0.015625