make queries using CASE WHEN faster

Question

I have this query

SELECT t1.date_added, t1.order_id, t1.firstname AS customer, t1.name AS product_name, t1.category, t1.supplier, t1.quantity, t1.price, t1.total
FROM (
     SELECT o.date_added, op.order_id, o.firstname, op.name, op.price, op.total, op.quantity, m.name AS supplier,
     (CASE WHEN skps.buy_price IS NULL THEN (SELECT skps2.buy_price FROM `oc_stock_kps` skps2 WHERE skps2.buy_price != '0' ORDER BY skps2.id DESC LIMIT 1)
           ELSE skps.buy_price
           END) AS buy_price, 
     (SELECT GROUP_CONCAT(cd.name SEPARATOR ' / ') FROM oc_category_description cd LEFT JOIN product_to_category ptc ON (ptc.category_id = cd.category_id) WHERE ptc.product_id = p.product_id) AS category,
     LAG(op.name) OVER(ORDER BY op.order_product_id) prev
FROM `oc_order_product` op
LEFT JOIN oc_order o ON (op.order_id = o.order_id)
LEFT JOIN oc_product p ON (op.product_id = p.product_id)
LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id)
LEFT JOIN oc_stock_kps skps ON (skps.product_id = op.product_id AND skps.order_id = op.order_id)
WHERE (o.date_added BETWEEN '2021-02-01 00:00:00' AND '2021-02-28 23:59:00')
    AND p.product_id != '0'
    AND o.order_status_id = '5'
) t1
WHERE t1.prev IS NULL OR t1.name<>t1.prev
ORDER BY t1.date_added
LIMIT 12

The problem is every time I'm using this

(CASE WHEN skps.buy_price IS NULL
    THEN (SELECT skps2.buy_price FROM `oc_stock_kps` skps2
                WHERE skps2.buy_price != '0'
                ORDER BY skps2.id DESC LIMIT 1)
    ELSE skps.buy_price
    END) AS buy_price,

and this

LEFT JOIN oc_stock_kps skps
     ON (skps.product_id = op.product_id
     AND skps.order_id = op.order_id)

it making queries took too long.
Is there any way to make it faster?

Having `AND` in the join condition can hurt performance. You could try adding appropriate indices, or maybe rethink your design to avoid the need for complex joins. — Tim Biegeleisen, Mar 14 '21 at 08:14
This won't scale very well. All the rows of `t1` will have to be computed before the oldest 12 can be picked. However, you already know that these will be the oldest 12 orders, so you could restrict your question to only work with these. In other words, move the sorting and limit to work inside `t1`, not outside of it. — KIKO Software, Mar 14 '21 at 09:03

score 0 · Answer 1 · answered Mar 14 '21 at 23:57

The sluggishness is probably in the subquery in the CASE, not the CASE, itself.

Composite and covering indexes to add. Put the columns in the given order:

o:  (order_status_id, date_added, order_id, firstname)
skps:  (order_id, product_id, buy_price)
p:  (product_id, manufacturer_id)

if product_to_category and oc_order_product are a many-to-many mapping tables, see the rules for optimizing the indexes here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

I don't see that the grouping is needed in this:

GROUP_CONCAT(cd.name SEPARATOR ' / ')

After making those changes, and you want to discuss further, please provide SHOW CREATE TABLE for each of the tables. And EXPLAIN SELECT ... for the query.

How many rows in each table? How many in the result without the final LIMIT 12?

score 0 · Answer 2 · answered Mar 15 '21 at 00:30

There may be some benefit to using CTE. If not in performance, at least in readability. But to get a better insight of where the bottleneck lies you should get the execution plan EXPLAIN SELECT. You may also need additional indexes but it's hard to tell without knowing table structure and the volume of data.

Why are you quoting fields that evidently seem to be integers ? This may be part of your problem, here is why. The date values should be quoted, this is OK but no the numbers.

make queries using CASE WHEN faster

2 Answers2