It's just occurred to me that the IN clause is not a constant. This probably throws out any chance of using an index for group by?
Cheers -----Original Message----- From: Andrew Armstrong [mailto:[EMAIL PROTECTED] Sent: Sunday, 29 July 2007 1:07 PM To: mysql@lists.mysql.com Subject: Using index for group-by: Not working? 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
