No. The fastest is to do "count(*)". -- D. Richard Hipp Sent from phone - Excuse brevity On Dec 13, 2014 11:13 AM, "Jim Callahan" <jim.callahan.orla...@gmail.com> wrote:
> So, if I understand the discussion the fastest way to get a count from the > command line interface (CLI) is to count the rows in the primary key, > assuming you have a primary key and that it is not a composite key. > > SELECT COUNT(primarykey) FROM table1 > > The "primarykey" in the above example is a stand in for the actual name of > the field designated as the primary key. > > I am also relying on the answers to FAQ #1 and #26. > > #26 The unique columns have non-null values (the answer says a lot more, > but that is the essence of what I am relying on). > > #1 If you have an integer primary key (which by definition in SQLITE3 > autoincrements) one might be able to get an approximate row count faster > using the: > > sqlite3_last_insert_rowid() > > function. > > Jim Callahan > Orlando, FL > > On Dec 13, 2014 10:17 AM, "Simon Slavin" <slav...@bigfraud.org> wrote: > > > > > On 13 Dec 2014, at 12:38pm, Richard Hipp <d...@sqlite.org> wrote: > > > > > Also, if there are indices available, SQLite attempts to count the > > smallest > > > index (it has to guess at which is the smallest by looking at the > number > > > and declared datatypes of the columns) and counting the smallest index > > > instead, under the theory that a smaller index will involve less I/O. > > > > Would it not be faster to just count the number of pages each index takes > > up ? Uh ... no. > > Wow. You really don't like storing counts or sizes, do you ? > > > > > To do better than this requires, as far as I know, an incompatible file > > > format change and/or a performance hit for applications that do not use > > the > > > feature. > > > > Can you tell us whether the problem exists in SQLite4 ? I know it uses a > > different format for indexes. I tried checking the documentation but > > didn't see an answer that didn't involve more work than I felt like > doing. > > > > Simon. > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users