On Jan 21, 2010, at 1:54 AM, Pavel Ivanov wrote: >> Why the difference in search time between searching individually and >> searching together? > > Apparently SQLite is not smart enough to optimize the search for both > min and max to make double entrance to the index - first from the > beginning, then from the end. It does search through the full index > instead which is not much better than search through the full table > (as it does without the index). But when searched separately SQLite > understands that it can pick up just first or just last entry from the > index.
Exactly right. http://www.sqlite.org/optoverview.html#minmax > > > Pavel > > On Wed, Jan 20, 2010 at 1:47 PM, Robert Citek > <robert.ci...@gmail.com> wrote: >> 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 >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
