MySQL group is very slow

Question

I have folowwing SQL query

SELECT CustomerID FROM sales WHERE `Date` <= '2012-01-01' GROUP BY CustomerID 

The query runs on 11,400,000 rows and runs very slowly. It takes more than 3 minutes. If I delete the group part, it runs below 1 second. Why is this?

MySQL server version is "5.0.21-community-nt"

 Here is the table schema: CREATE TABLE `sales` ( `ID` int(11) NOT NULL auto_increment, `DocNo` int(11) default '0', `CustomerID` int(11) default '0', `OperatorID` int(11) default '0', PRIMARY KEY (`ID`), KEY `ID` (`ID`), KEY `DocNo` (`DocNo`), KEY `CustomerID` (`CustomerID`), KEY `Date` (`Date`) ) ENGINE=MyISAM AUTO_INCREMENT=14946509 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

Answer 1

Try entering an index (Date, CustomerID).

See the mysql manual for query group optimization : - Group by optimization

You can find out how mysql generates the result if you use EXPLAIN as follows: -

 EXPLAIN SELECT CustomerID FROM sales WHERE `Date` <= '2012-01-01' GROUP BY CustomerID 

This will tell you which indexes (if any) mysql uses to optimize the query. This is very convenient when learning which indexes work for which queries, how you can try to create an index and see if mysql uses it. Therefore, even if you do not fully understand how mysql calculates aggregated queries, you can create a useful index by trial and error.

Answer 2

Not knowing what your table layout looks like, it's hard to be sure, but it will probably help if you add an index with multiple columns to Date and CustomerID . This would save MySQL from having to perform a full table scan for the GROUP BY . Try ALTER TABLE sales ADD INDEX (Date,CustomerID) .

Answer 3

try the following:

 SELECT distinct CustomerID FROM sales WHERE `Date` <= '2012-01-01' 

Answer 4

I had the same problem, I changed the key fields to the same sort and fixed the problem. Fields for joining tables had a different meaning for sorting.

Answer 5

Wouldn’t it be much faster and achieve the same?

 SELECT DISTINCT CustomerID FROM sales WHERE `Date` <= '2012-01-01' 

Be sure to put the index on Date , of course. I'm not quite sure, but indexing CustomerID can also help.