It is faster because if it knows there is no where or join or whatever row limiting condition and it also knows there is a unique index on all fields it can simply do select count(rowid) from table1 and not do any count distinct.
RBS On Fri, Oct 2, 2015 at 3:51 PM, Scott Hess <shess at google.com> wrote: > On Fri, Oct 2, 2015 at 7:43 AM, Bart Smissaert <bart.smissaert at gmail.com> > wrote: > > > > The Uniqueness of the output depends on which fields are included, > JOINs, > > UNIONs, etc. etc. > > > > I am not talking about that situation. I am only referring to a situation > > where you want to count all > > rows in a table. I know it will be uncommon to have an index on all > fields > > and this is not really a practical > > question. I suppose as it so uncommon it is not worth it to put this > > optimization in. > > > Is your case of having the unique index on all fields faster than having a > unique index on a single field? > > Maybe you should include an example of your schema. I can't think of how > scanning an index on all fields could be smaller than the underlying table, > so it's unclear how that could be faster. But a unique index on a subset > of the data could be faster simply from being smaller. > > Also, 10x faster makes me wonder about whether you're accounting for > caching effects. A blunt way to test for that is to run your queries a > couple times. If the first time is slow and the second and later times are > much faster, then it's likely the cache is causing the speedup. > > -scott > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
