RE: Using index for group-by: Not working?

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:andrew@mammoth.com.au] 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

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

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

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.  

Do you need more help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

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/mysql?unsub=lists@pantek.com