On Fri, Apr 04, 2008 at 09:14:52AM -0700, Scott Hess scratched on the wall: > What I meant when I said "full table scan" is that it has to read at > least something for every single row in the table. So the following > are going to be the same: > > SELECT COUNT(*) FROM t; > SELECT COUNT(rowid) FROM t;
These are extremely similar, but not the same. SELECT COUNT(*) FROM t; uses a version of "count" that expects zero arguments. The core of the operation looks like this: 3 OpenRead 0 2 0 00 4 SetNumColumns 0 0 0 00 5 Rewind 0 8 0 00 6 AggStep 0 0 1 count(0) 00 7 Next 0 6 0 00 8 Close 0 0 0 00 9 AggFinal 1 0 0 count(0) 00 10 SCopy 1 2 0 00 11 ResultRow 2 1 0 00 12 Halt 0 0 0 00 In specific, the "main loop" is just AggStep and Next over and over. It just walks the B-Tree and never deals with row records. SELECT COUNT(rowid) FROM t; uses a version of "count" that expects one argument. In this case, the value of "rowid". The core of that operation looks like this: 4 OpenRead 0 2 0 00 5 SetNumColumns 0 0 0 00 6 Rewind 0 10 0 00 7 Rowid 0 3 0 00 8 AggStep 0 3 1 count(1) 01 9 Next 0 7 0 00 10 Close 0 0 0 00 11 AggFinal 1 1 0 count(1) 00 12 SCopy 1 3 0 00 13 ResultRow 3 1 0 00 14 Halt 0 0 0 00 In this case there is an extra step in the loop, as the rowid is fetched and passed to count. As I understand it, that rowid value is taken directly out of the B-Tree, however, so you still don't need to read the actual row-record data. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
