Re: Group by not using index

[Rick James]

set @param1 = 0 ;
set @param2 = 0;
set @param3 = 2;
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;

1. Construct the query (instead of using @variables to pick what is happening)

2. Turn "IN ( SELECT ... )" into a JOIN

3. Do EXPLAIN EXTENDED SELECT ...; SHOW WARNINGS;
That will provide the compiled query. I am guessing it is like the second one above??
Perhaps it decided to evaluate the subquery when it does not need to.

Is this equivalent?

SELECT  tab2.col1 ,
        max(tab1.`col2`)
    FROM  tab1
    JOIN  tab2  ON tab1.`col1` = tab2.`col1
    WHERE     tab2.col3 = 1
    GROUP BY  tab2.col1;

If it is, then tab2 needs
INDEX(col3, col1)