On 2014/12/13 14:38, Richard Hipp wrote:
The "SELECT count(*) FROM table" query already has a special optimization in the b-tree layer to make it go faster. You can see
this by comparing the times of these queries:
SELECT count(*) FROM table;
SELECT count(*) FROM table WHERE 1;
The WHERE clause on the second query disables the optimization and so the second query should run slower. The second query visits
and partially decodes every row in the b-tree. The first visits every leaf page of the b-tree, but it does nothing more than read
the "number-of-entries" from the header of the page, add that value to the accumulating count, and then move on.
Thank you very much for the clear explanation and taking the time Richard. The proposed methods above are fast indeed, but not
instant and I guess you will never satisfy everyone until everything works instantaneously. I take the point that the only possible
improvements seem to need alteration to the file structure or added maintenance which may use up cycles for something that just
isn't that important to DB use in general - and I have to agree, I too have zero want for seeing more cycles used. I was hoping
there might be a way, but am satisfied there isn't and would like to hope out loud with Simon that this might be seen in SQLite4.
Thanks,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
