Group by count() and indexes

Group by count() and indexes

Post by Anuradha Ratnawee » Thu, 20 Feb 2003 03:17:51

Consider the following query on a large table with lots of different

    SELECT id FROM my_table GROUP BY id ORDER BY count(id) LIMIT 10;

It has an (usually unique) index on id.  Obviously, the index helps to
evaluate count(id) for a given value of id, but count()s for all the
`id's should be evaluated, so sort() will take most of the time.

Is there a way to improve performance of this query?  If not, please
give some indication to do a workaround on the source itself, so perhaps
I may be able to work out a patch.

Thanks in advance.



Debian GNU/Linux (kernel 2.4.21-pre4)

It is contrary to reasoning to say that there is a vacuum or space in
which there is absolutely nothing.
                -- Descartes

---------------------------(end of broadcast)---------------------------