Group by, count, order by and limit

Поиск
Список
Период
Сортировка
Искать

Group by, count, order by and limit

От:
Anuradha Ratnaweera <anuradha@lklug.pdn.ac.lk>
Дата:

My 3rd attempt to post ...

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.

Thanks in advance.

	Anuradha

-- 

Debian GNU/Linux (kernel 2.4.21-pre4)

There are three ways to get something done:
	(1) Do it yourself.
	(2) Hire someone to do it for you.
	(3) Forbid your kids to do it.

FAQ