SQLite資料庫怎麼這麼快？

才一個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