sqlite3 timing

From my shell session:

[[[
% time sqlite3 foo.db "SELECT hash, revision, offset, size, expanded_size FROM rep_cache;" > /dev/null
sqlite3 foo.db > /dev/null 12.72s user 0.69s system 98% cpu 13.575 total

% time sqlite3 foo.db "SELECT hash, revision, offset, size, expanded_size FROM rep_cache;" > /dev/null
sqlite3 foo.db > /dev/null 12.56s user 0.94s system 99% cpu 13.561 total

% time sqlite3 foo.db "SELECT hash, revision, offset, size, expanded_size, COUNT(*) FROM rep_cache;" > /dev/null
sqlite3 foo.db > /dev/null 2.76s user 0.72s system 99% cpu 3.492 total

% time sqlite3 foo.db "SELECT hash, revision, offset, size, expanded_size, COUNT(*) FROM rep_cache;" > /dev/null
sqlite3 foo.db > /dev/null 2.79s user 0.69s system 99% cpu 3.498 total

% time sqlite3 foo.db "SELECT hash, revision, offset, size, expanded_size FROM rep_cache;" > /dev/null
sqlite3 foo.db > /dev/null 12.62s user 0.75s system 99% cpu 13.430 total

% sqlite3 -version
3.7.4
]]]

That is: adding COUNT(*) to the query makes it 3.9 times faster.

Huh? How does that make sense? Should we do anything differently in
our code in light of this?

[ This isn't a fabricated example --- this is the query I added to
rep-cache.c today ]
Received on 2011-07-14 04:55:07 CEST