Search code examples
mysqlgroupwise-maximum

inner join optimization after group by the subquery

so I've been working on optimize the inner join with the subquery which has group by statement. the query below it takes around 1.8 to 2 sec to fetch. I would like to optimize it and I think the subquery would be a key.

I'm not sure the subquery with group by in inner join can use index to join the other table. what I believe is that column in subquery (which is A2, C2 in this case) can not have a its own index inside inner join. is that correct?

so, my question is how can I optimize this query statement and is that possible to set index on A2, C2 in inner join. 

SELECT A, C, X.S
FROM tb_g X 
INNER JOIN ( 
    SELECT A AS A2, MAX(C) AS C2
    FROM tb_g
    GROUP BY A 
) Y 
ON X.A = Y.A2 AND X.C = Y.C2;

enter image description here

enter image description here

Solution

  • A composite index on A, C should allow this query to be optimized as well as possible:

    ALTER TABLE tb_g ADD INDEX (A, C);

    This index allows the subquery to be calculated entirely with the index, and then the join with the intermediate table can be done with optimal fetching in the original table.