Explain Query (mysql)
EXPLAIN SELECT
od.product_id,
SUM(IF(o.delivery_date BETWEEN SUBDATE(CURDATE(), INTERVAL 1 YEAR) AND CURDATE(), 1, 0)) AS nb_purchases_since_last_year,
COUNT(od.order_details_id) AS nb_purchases_since_begining
FROM
`order_details` od
INNER JOIN orders o ON (o.order_id = od.order_id AND o.status = 4 AND o.user_id = -1)
INNER JOIN products p ON (od.product_id = p.product_id AND p.status = 1)
WHERE
od.`defined_retail_price_per_unit_for_default_weight` > 0 AND od.product_id NOT IN (SELECT DISTINCT hd_product_id FROM droppoints WHERE hd_product_id IS NOT NULL)
GROUP BY
od.product_id
ORDER BY
SUM(IF(o.delivery_date BETWEEN SUBDATE(CURDATE(), INTERVAL 1 YEAR) AND CURDATE(), 1, 0)) DESC
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | o | ref | PRIMARY, idx_user_id, ids_status, idx_status_delivery_date_subscription_type, orders_idx_status_droppoint_delivery | idx_user_id | 4 | const | 1 | 50.00 | Using where; Using temporary; Using filesort | |
| 1 | PRIMARY | od | ref | idx_order_id, idx_product_id | idx_order_id | 4 | securelufacom.o.order_id | 15 | 33.33 | Using where | |
| 1 | PRIMARY | p | eq_ref | PRIMARY, idx_status | PRIMARY | 4 | securelufacom.od.product_id | 1 | 20.98 | Using where | |
| 2 | SUBQUERY | droppoints | range | fk_dp_hd_product | fk_dp_hd_product | 5 | 35 | 100.00 | Using where; Using index |