Explain Query (mysql)
EXPLAIN SELECT
o.order_id,
r.value as rating,
o.delivery_date
FROM
orders o
LEFT JOIN
ratings r
ON r.order_id = o.order_id
AND rating_type = 0
AND rating_feedback_type_id = 1
WHERE
delivery_date > '2025-04-03' AND
o.delivery_date = (SELECT MAX(delivery_date) FROM orders WHERE user_id = '308842' AND status = 4 AND gift_receiver_id IS NULL AND real_delivery_time != "0000-00-00 00:00:00") AND
o.status = 4 AND
o.user_id = '308842' AND
r.order_id IS NULL AND
o.real_delivery_time != "0000-00-00 00:00:00" AND
gift_receiver_id IS NULL
ORDER BY order_id DESC
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | o | index_merge | idx_delivery_date, idx_user_id, ids_status, orders+delivery_date&status, idx_status_delivery_date_subscription_type, orders_idx_status_droppoint_delivery | orders+delivery_date&status, idx_user_id | 5, 4 | 1 | 9.00 | Using intersect (orders+delivery_date&status, idx_user_id); Using where; Using filesort | ||
| 1 | PRIMARY | r | ref | f_key_ratings_order_id, f_key_rating_feedback_type_id | f_key_ratings_order_id | 5 | securelufacom.o.order_id | 6 | 100.00 | Using where | |
| 2 | SUBQUERY | orders | ref | idx_user_id, ids_status, idx_status_delivery_date_subscription_type, orders_idx_status_droppoint_delivery | idx_user_id | 4 | const | 213 | 4.50 | Using where |