>
> Consider this query on a large table with lots of different IDs:
>
>     SELECT id FROM my_table GROUP BY id ORDER BY count(id) LIMIT 10;
>
> It has an 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 come out with a patch.
>
Is there a difference in performance if you re-write it as

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

?

Regards, Christoph



Tue, 18 Feb 2003 11:18:05 +0100
Christoph Haller <ch@rodos.fzk.de>
Re: Group by, count, order by and limit