Hey Terry, 1) I've tried placing the Count/Distinct bit first - no change. Could you elaborate on your "bottom up" parsing? I am not aware of this myself (and have not read this anywhere). Would be interested if you could elaborate or provide more info.
2) When a GROUP BY is performed, sorting is done also (usually because its "free"). Sorting by NULL will tell MySQL to not even bother doing this. I've seen 'sort by null' (as suggested elsewhere) avoid 'Using filesort' under the Extra column when its not even needed. Thanks for the response. -----Original Message----- From: Terry Mehlman [mailto:[EMAIL PROTECTED] Sent: Sunday, 29 July 2007 1:18 PM To: Andrew Armstrong Subject: Re: Using index for group-by: Not working? just a shot in the dark, but i would suggest two changes to your query. 1) put the count (distinct c5) first rather than last. as i'm sure you know the parse happens from the bottom up. so, the indexes you are placing on c2, c3, and c4 aren't doing you any good if you put the distinct clause in their way. 2) order by null? take that out and see if your performance improves. just a couple of thoughts. On 7/28/07, Andrew Armstrong <[EMAIL PROTECTED]> wrote: > Hi, > > > > I have the following query: > > > > SELECT c2, c3, c4, Count(DISTINCT c5) > > FROM table1 > > WHERE c1 IN (1, 2, 3...) > > GROUP BY c2, c3, c4 > > order by null > > > > Yet I can only get it at best to show (under extra): Using where, using > filesort. > > > > I have read up on: > http://dev.mysql.com/doc/refman/5.0/en/loose-index-scan.html and > http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html and > http://dev.mysql.com/doc/refman/5.0/en/explain.html yet cannot get this > index to be used. > > > > I am running MySQL '5.1.17-beta-community-nt-debug' > > > > There are over 600,000 rows in table1 for my testing. > > > > I have tried placing indexes on: idx(c2, c3, c4, c5) and idx(c1, c2, c3, c4, > c5) and indx(c5, c1, c2, c3, c4) with no result. > > > > Is there a reason I cannot get this query to use an index for grouping? > > > > Cheers, > > Andrew > > -- That which Voldemort does not value, he takes no trouble to comprehend. Of house-elves and children's tales, of love, loyalty, and innocence, Voldemort knows and understands nothing. Nothing. That they all have a power beyond his own, a power beyond the reach of any magic, is a truth he has never grasped. - JK Rowling -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
