On Wed, Jan 20, 2010 at 8:52 AM, Max Vlasov <max.vla...@gmail.com> wrote: > I thought at the first moment that Pavel's suggestion on using extra index > is very "expensive" in terms of megabytes on the disk, but despite this > drawback it's actually more robust.
For my own curiosity I created a table with random text data and discovered there was only a negligible difference between using indexes and not when search for min and max in the same query. However, when searching for min or max in separate queries the answer was almost instantaneous. (details below). Why the difference in search time between searching individually and searching together? Regards, - Robert ----- $ sqlite3 sample.db 'drop table if exists sample ; create table sample (foo text) ; ' $ jot -r -c 80000000 a z | tr -d '\n' | fold -w 8 | fmt -w 8 | sqlite3 sample.db '.imp "/dev/stdin" "sample"' $ sqlite3 sample.db 'select count(*) from sample ; ' 10000000 $ time -p sqlite3 sample.db 'select min(foo), max(foo) from sample ; ' aaaaaaaq|zzzzytyd real 3.24 user 3.10 sys 0.11 $ time -p sqlite3 sample.db 'create index sample_foo on sample (foo) ; ' real 838.92 user 53.68 sys 38.46 $ time -p sqlite3 sample.db 'select min(foo), max(foo) from sample ; ' aaaaaaaq|zzzzytyd real 3.19 user 3.13 sys 0.06 $ time -p sqlite3 sample.db 'select min(foo) from sample ; ' aaaaaaaq real 0.00 user 0.00 sys 0.00 $ time -p sqlite3 sample.db 'select max(foo) from sample ; ' zzzzytyd real 0.00 user 0.00 sys 0.01 $ sqlite3 --version 3.6.10 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
