On 2015-10-02 10:05 AM, Bart Smissaert wrote: > Noticed that if I have table with a unique index on all fields, counting > all rows is still a lot faster > (about a factor 10 on my particular test table) than counting distinct rows. > Could maybe an optimization be added to SQLite to speed this up, taking > advantage of the fact that there is a unique index on all fields? > I am running SQLite 3.8.10.
I'm sure it can and I'm not answering (since the devs may do whatever they decide), but I would like to mention that a Unique index on all fields is: A - More uncommon than Lotto-winners. B - In no way related to a DISTINCT specifier. The DISTINCT specifier checks that the results (output list) from a Query is Unique - There is no way to determine that from the Input table's uniqueness. In fact ALL tables with a Primary Key is by definition row-unique, but it has no bearing on the output of a query from it. The Uniqueness of the output depends on which fields are included, JOINs, UNIONs, etc. etc. In other words: Considering the above points, I believe this is an optimization that would serve so small a possible set of use-cases as to not deserve the few bytes needed to implement it.
