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; It won't have to scan any overflow pages, but it will have to hit all the leaf nodes. You could certainly do a full scan on an index other than the rowid. It might involve much less reading if the indexed items are small relative to the overall row. Not sure if SQLite does this optimization for you or not (I don't think it much matters - it's still going to bel O(N), just with a lower constant). -scott On Fri, Apr 4, 2008 at 8:19 AM, Samuel Neff <[EMAIL PROTECTED]> wrote: > Scott, > > Is it really a full table scan or just an index scan (at least in the case > where no data is needed from the table as in the original sample that had no > join or where clause). > > Thanks, > > Sam > > > > On Thu, Apr 3, 2008 at 4:12 PM, Scott Hess <[EMAIL PROTECTED]> wrote: > > > A little bit more info: SELECT COUNT(*) is implemented as a full > > table scan, so SQLite is visiting every row in the table, which will > > get slower and slower as the table gets bigger and the database > > fragments. This differs from many database engines (which implement > > an optimization for this) Doing the trigger thing means that it only > > visits the specific row that contains the count. > > > > -scott > > > > > -- > ----------------------------------------------------------------- > We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer. > Position is in the Washington D.C. metro area. Contact > [EMAIL PROTECTED] > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
