Explain Query (mysql)
EXPLAIN SELECT
COUNT(*) as units_bought,
pc.category_id,
IFNULL(pcL.l_name,pc.name) as cat_name
FROM order_details od
LEFT JOIN products p
ON p.product_id = od.product_id
LEFT JOIN productSubSubCategories pssc
ON pssc.sub_sub_id = p.sub_sub_id
LEFT JOIN productSubCategories psc
ON psc.subcategory_id = pssc.subcategory_id
LEFT JOIN product_categories pc
ON pc.category_id = psc.category_id
LEFT JOIN productCategoriesLang pcL
ON pcL.category_id = pc.category_id AND pcL.lang_id = 'fr'
WHERE
order_id IN (
SELECT order_id
FROM orders
WHERE
status = 4
AND delivery_date >= SUBDATE(CURDATE(), INTERVAL 7 DAY)
AND user_id IN (SELECT user_id FROM users WHERE charitable_account = 1)
)
AND p.virtual = 0
GROUP BY pc.category_id
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | orders | range | PRIMARY, idx_delivery_date, idx_user_id, ids_status, orders+delivery_date&status, idx_status_delivery_date_subscription_type, orders_idx_status_droppoint_delivery | idx_status_delivery_date_subscription_type | 5 | 79616 | 100.00 | Using index condition; Using temporary | ||
| 1 | SIMPLE | users | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.orders.user_id | 1 | 10.00 | Using where | |
| 1 | SIMPLE | od | ref | idx_order_id, idx_product_id | idx_order_id | 4 | securelufacom.orders.order_id | 15 | 100.00 | ||
| 1 | SIMPLE | p | eq_ref | PRIMARY, idx_virtual_products | PRIMARY | 4 | securelufacom.od.product_id | 1 | 50.00 | Using where | |
| 1 | SIMPLE | pssc | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.p.sub_sub_id | 1 | 100.00 | ||
| 1 | SIMPLE | psc | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.pssc.subcategory_id | 1 | 100.00 | ||
| 1 | SIMPLE | pc | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.psc.category_id | 1 | 100.00 | ||
| 1 | SIMPLE | pcL | ref | category_id | category_id | 4 | securelufacom.pc.category_id | 1 | 100.00 | Using where |