Re: [HACKERS] Group by, count, order by and limit
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Group by, count, order by and limit
On Tue, Feb 18, 2003 at 10:26:46 +0600, Anuradha Ratnaweera [EMAIL PROTECTED] wrote: 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. In 7.4 there is a hash method that can be used for aggregates. This may help a lot in your case if there aren't a lot of distict IDs. 7.4 is a long way from even a beta, but you still might want to play with it to see if it will solve your problem down the road. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Group by, count, order by and limit
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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
