how to optimize the slow query?

Question

The below query is taking time to execute. does this have any rewrite possibilities?

    Query;
    SELECT  t_product.a_productid, 
            t_product.a_mpactive, 
            t_product.a_active, 
            trim( substring_index(a_reference, '_',-1)) as a_reference, 
            t_product.a_shopid, 
            t_productlang.a_name, 
            t_deactivatedproduct.a_reason 
    FROM  t_deactivatedproduct 
    inner join ( SELECT  max(a_deactivatedproductid) as a_deactivatedproductid 
                 FROM  t_deactivatedproduct 
                 GROUP by  t_deactivatedproduct.a_productid
                ) as a on a.a_deactivatedproductid = t_deactivatedproduct.a_deactivatedproductid 
    INNER JOIN t_product ON t_product.a_productid = t_deactivatedproduct.a_productid 
    INNER JOIN t_productlang ON t_product.a_productid = t_productlang.a_productid 
    AND t_product.a_shopid IN( 2, 3, 5, 6, 7, 10, 8, 15, 12, 16, 17, 26, 27, 28) 
    WHERE  t_product.a_ispublished = 1 
    AND ( (  t_product.a_active = 1 AND t_product.a_mpactive = 0) OR (t_product.a_active = 0  AND t_product.a_mpactive = 1) 
    OR ( t_product.a_active = 0  AND t_product.a_mpactive = 0 ) ) 
    ORDER BY  t_deactivatedproduct.a_deactivatedproductid DESC 
    limit  700

can someone please tell me where has a problem with it and how to change it?

Answer 1

All the tables are using indexes, so there's not a lot to optimize.

I see you're trying to use a derived table to find the greatest id per productid. I would write it this way instead:

SELECT ...
FROM 
  t_deactivatedproduct AS d1
  LEFT OUTER JOIN t_deactivatedproduct AS d2
    ON d1.a_productid = d2.a_productid
    AND d1.a_deactivatedproductid < d2.a_deactivatedproductid
...
WHERE d2.a_deactivatedproductid IS NULL
...

This is a method of getting the greatest row per group without using subqueries. Compare two rows d1 and d2. d2 must have the same productid and a greater deactiveatedproductid. If there is no such row, then the outer join will return NULL for its columns. Therefore you know that d1 has the greatest id in that group.

The problem with your method is that it creates a temporary table for the result of the subquery, and that's costly. I've found that which method is faster depends on the size of the groups and the number of distinct groups, but the method I show above can be faster than the derived table method you are using. See also my answer to Retrieving the last record in each group - MySQL