I have a table with 2.4 million records. It’s a without rowid table (I don’t know if that’s significant) with an integer primary key (ID) and several secondary indexes of the form (OtherCol, ID). If I run
select min(ID), max(ID), count(*) from BigTbl; It takes 0.67 secs If I run the three commands separately select min(ID) from BigTbl; select max(ID) from BigTbl; select count(*) from BigTbl; the TOTAL time to run all 3 is around 0.1 secs. explain query plan select min(ID) from BigTbl; suggests the primary key is used explain query plan select max(ID) from BigTbl; suggests the primary key is used explain query plan select count(*) from BigTbl; suggests a secondary index (call it Ndx) is used Any combo also seems to use secondary index Ndx e.g. explain query plan select min(ID), max(ID) from BigTbl; and explain query plan select min(ID), max(ID), count(*) from BigTbl; both use secondary index Ndx. All come up with the correct answer but obviously when Ndx is used min and max require checking all values of ID rather than obtaining the result from first and last entries in primary key. Tom _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
