On Thu, Jun 06, 2013 at 04:52:12PM +0200, Gabriel Corneanu scratched on the
wall:
> I was surprised to find that simple query "select count(*) from table" took
> too much time, and found that it does NOT use the primary key index??
> e.g.
> CREATE TABLE t(a);
> explain query plan select count(*) from t
>
> I get : SCAN TABLE t (~1000000 rows)
>
> If I use CREATE TABLE t(a unique), then it uses the auto-generated cover
> index.
> Even if I write
> select count(rowid) from t
> it still uses scan table...
> However I would expect that it should also use the primary key for
> counting, or not??
What PK? Rowid is not a PK unless you define it as such. The table
itself is stored in rowid order, so the "index" for rowid is the
table itself-- there is no "other" index for rowid.
> In my opinion, count(*) is the same as count(rowid) (I see that even
> count() is accepted); I could say it's even the same as count(x) (any other
> field).
That is not true. The SQLite docs are quite clear:
http://www.sqlite.org/lang_aggfunc.html#count
count(X)
count(*)
The count(X) function returns a count of the number of times that X
is not NULL in a group. The count(*) function (with no arguments)
returns the total number of rows in the group.
If you provide an actual column name, count() only counts non-NULL
rows. The two versions of the function are equivalent if "X"
prohibits NULL entries (such as the rowid column), but not in the
general case.
This is not SQLite specific... this is standard SQL.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
