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?

@ErgestBasha I guess there's a rule about the ratio of code to text required in a post. — Bill Karwin, Oct 28 '22 at 15:16
It is known that `OR` operator is a performance killer , try using union instead — Ergest Basha, Oct 28 '22 at 15:21
@ErgestBasha In this case the `OR` is not causing a table-scan as it does sometimes. The join to `t_product` is using the primary key anyway (type: eq_ref). — Bill Karwin, Oct 28 '22 at 15:24

score 1 · Accepted Answer · answered Oct 28 '22 at 15:23

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

thank you for the answer and the explanation let me check. – RRQ Oct 28 '22 at 16:37 — RRQ, Oct 28 '22 at 16:37
thank you so much that was brilliant @Bill Karwin – RRQ Oct 28 '22 at 17:07 — RRQ, Oct 28 '22 at 17:07

score 1 · Answer 2 · answered Oct 28 '22 at 20:54

Improved indexes:

t_productlang:  INDEX(a_productid,  a_name)
t_deactivatedproduct:  INDEX(a_deactivatedproductid, a_productid,  a_reason)
t_deactivatedproduct:  INDEX(a_productid, a_deactivatedproductid)

(These handle Bill's suggested change, too.)

Because of the ORs, probably no index on t_product is useful.

For further discussion, please provide SHOW CREATE TABLE and EXPLAIN SELECT ...

how to optimize the slow query?

2 Answers2