On 14/09/12 22:56, Clemens Ladisch wrote:
But do you have any numbers (which would help deciding whether to accept this
patch)?
I've run two queries on two different DBs:
- DB1: Size 414M, Count of recs 2999671, the table has 17 Cols
- DB2: Size 1.4G, Count of recs 1975986, the table has 28 Cols
The tables had one of the columns indexed (Column Cidx)
Queries run were:
Tablescan (Ts) query:
select sum(Cidx) from t;
SCAN TABLE t (~1000000 rows)
CoveringIndexscan (CIs) query:
select sum(Cidx) from (select Cidx from t order by Cidx);
SCAN TABLE t USING COVERING INDEX idx_Cidx (~1000000 rows)
SCAN SUBQUERY 1 (~1000000 rows)
For each DB, i've run 3 times each query (below i present an average),
clearing the OS's caches every time.
DB1:
Ts: ~ 4 sec 100 msec.
CIs: ~ 2 sec 90 msec
DB2:
Ts: ~ 4 sec 550 msec.
CIs: ~ 1 sec 350 msec
Apart from the covering index scans being faster, another interesting
fact that i've found is that when both table's and index's data are in
memory (cached), the CIs query is consistently two times slower than Ts.
A rough guess is that SQLite's query execution VM has to do more work
due to not being able to optimize:
SCAN TABLE t USING COVERING INDEX idx_Cidx (~1000000 rows)
SCAN SUBQUERY 1 (~1000000 rows)
to just:
SCAN TABLE t USING COVERING INDEX idx_Cidx (~1000000 rows)
Regards,
lefteris.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
