Skip navigation links

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


Advanced Search

Re: Group by not using index
Posted by: Rick James ()
Date: March 04, 2012 10:59PM

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)

Options: ReplyQuote


Subject Views Written By Posted
Group by not using index 1335 Matthew Moles 03/02/2012 10:06AM
Re: Group by not using index 557 irek kordirko 03/03/2012 05:22PM
Re: Group by not using index 686 Rick James 03/04/2012 10:59PM
Re: Group by not using index 609 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.