Group by not using index
Hi all,
I have a problem with my query not using the key for a group by clause, the table structure and query is as follows:
CREATE TABLE `tab1` (
`col1` int(11) NOT NULL AUTO_INCREMENT,
`col3` int(11) NOT NULL,
`col2` tinyint(4) NOT NULL,
PRIMARY KEY (`col1`),
KEY `ess_sig_ind` (`col2`), ) ENGINE=InnoDB
CREATE TABLE `tab2` (
`Col1` int(11) NOT NULL,
`Col2` datetime NOT NULL,
`col3` tinyint(4) NOT NULL,
`col4` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`col2`,`col1`,`col3`),
KEY `index1` (`col1`,`col2`),) ENGINE=InnoDB
set @param1=0 ;
set @param2 = 0;
set @param3 = 2;
SET @param4 = '2012-03-01 23:59:00';
set @@param5 = '2020-03-01 23:59:00';
set @param6 = SUBTIME(@param5, SEC_TO_TIME(1440 * 60));
explain SELECT tab2.col1 , max(tab1.`col2`)
FROM tab1 JOIN tab2 ON tab1.`col1` = tab2.`col1
WHERE (tab1.col1 = @param1 OR @param1 = 0)
AND ( (@param3 = 0 AND tab1.col2 = 0)
OR (@param3 = 1 AND (tab1.col2 = 0
OR tab1.col2 = 1)) OR ( @param3 = 2) )
AND (@param2 = 0 OR tab1.col3
IN ( SELECT tab3.col3
FROM tab3 ad
WHERE (tab3.col2 = @param2 OR @param2 = 0)))
--AND tab2.col2 < @param4
AND tab2.col3 = 1
--AND tab2.col4 < @param5
--AND tab2.col4 > @param6
GROUP BY tab2.col1;
Tab1 1 has 300 records
Tab 2 has 2 million in the data set for testing and will have about 50 million when final, the table is partitioned on tab2.col3 per month, with about 1.5 million records a month
With the commentedlines uncommented the explain is this:
id- Select_type -Table Type -Possible_keys- Key- Key_len -Ref- Rows- extras
1 -PRIMARY -Tab2- range PRIMARY,index1- PRIMARY- 8- 25928- Using where; Using index; Using temporary; Using filesort
1 -PRIMARY -Tab1 eq_ref- PRIMARY -PRIMARY- 4 -Tab2.col1- 1
2 -DEPENDENT SUBQUERY -Tab3 -ALL -356- Using where
With the commented lines commented the expalin is this
id -Select_type -Table-Type- Possible_keys- Key- Key_len- Ref- Rows -extras
1 -PRIMARY- Tab1- ALL- PRIMARY -365- Using temporary; Using filesort
1 -PRIMARY- Tab2 -ref- Index1-Index1- 4-Tab2.col1 -2696- Using where; Using index
2 -DEPENDENT SUBQUERY- tab3- ALL- 356- Using where
As you can see the extra parts of the where clause cause MYSQL to stop using the index for the group by part of the query.
I have tried FORCE INDEX FOR GROUP BY(index1) with no effect
Does anyone know why this is, and more importantly how to make it use the index?
p.s. sorry for the eariler format issues
Edited 2 time(s). Last edit at 03/05/2012 03:28AM by Matthew Moles.