On Sat, Jun 25, 2011 at 7:25 AM, Luuk <luu...@gmail.com> wrote: > I create a test database with almost 10 milion rows. > I'm surprised to see that the first SELECT below is much slower than the > sum of the next three SELECTs. > > Can anyone give a hint why this is the case? > > SQLite version 3.7.2 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> .timer on > sqlite> .schema test > CREATE TABLE test (i integer primary key); > sqlite> select min(i), max(i), count(i) from test; > 1|9999999|9999999 >
The above does a full table scan, looping through and reading every value of test.i, then incrementing a counter or recording the minimum or maximum value, as appropriate. > CPU Time: user 4.508429 sys 0.265202 > sqlite> select min(i) from test; > The above gets optimized to: SELECT i FROM test ORDER BY i ASC LIMIT 1 which is very fast. > 1 > CPU Time: user 0.000000 sys 0.000000 > sqlite> select max(i) from test; > The above gets optimized to: SELECT i FROM test ORDER BY i DESC LIMIT 1 which is also very fast > 9999999 > CPU Time: user 0.000000 sys 0.000000 > sqlite> select count(i) from test; > There is a special "count" procedure in the btree code that avoids having to read each entry - it merely counts them. This is still an O(N) operation but is much faster than loading each entry one by one. > 9999999 > CPU Time: user 1.497610 sys 0.390002 > sqlite> > > -- > Luuk > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
