Skip navigation links

MySQL Forums :: Optimizer & Parser :: Group by not using index


Advanced Search

Group by not using index
Posted by: Matthew Moles ()
Date: March 02, 2012 10:06AM

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.

Options: ReplyQuote


Subject Views Written By Posted
Group by not using index 1302 Matthew Moles 03/02/2012 10:06AM
Re: Group by not using index 550 irek kordirko 03/03/2012 05:22PM
Re: Group by not using index 677 Rick James 03/04/2012 10:59PM
Re: Group by not using index 601 Matthew Moles 03/05/2012 03:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.