Why does any of that matter? SELECT COUNT(*) FROM table; already knows all of that information.
If you have a question about why one query is faster/slower than another query given one schema versus another schema, then post representative schema and queries. Right now you're just throwing random terms around and hoping something sticks. Earlier you said that you weren't talking about DISTINCT as applied to result sets, but just now you're re-introducing COUNT DISTINCT for some reason. SQL code will make your question concrete. -scott On Fri, Oct 2, 2015 at 7:54 AM, Bart Smissaert <bart.smissaert at gmail.com> wrote: > 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 > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
