On 6/6/2013 11:26 AM, Gabriel Corneanu wrote:
Again sorry for count(a), I wrote too fast. I understand of course about
null values.
Otherwise by rowid I mean the autogenerated primary key. In my actual
case, I have a field as alias.
CREATE TABLE t(id integer primary key, a);
explain query plan select count(*) from t -> scan table
create index ia on t(id);
explain query plan select count(*) from t -> SCAN TABLE t USING COVERING
INDEX...
1. It means, the primary key is not as good as a cover index??
Again - there was *no* separate index until you explicitly created one.
Another way to look at it is that the table as a whole *is* in fact a
covering index for itself, ordered by rowid and covering all the fields
in the table. In this view, "scan table" is just a shorthand for "scan
table using covering index which is the table itself".
If you need this count real fast for some reason, then create a separate
table, with one column and one row, that would store the count. Then
create INSERT and DELETE triggers that would maintain the count.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
