Explain Query (mysql)
EXPLAIN SELECT
IFNULL(nb_shipped_since_message, 0) AS nb_shipped_since_message
FROM
`custom_message_to_display_on_checkout`
LEFT JOIN (
SELECT
user_id,
COUNT(*) AS nb_shipped_since_message
FROM
orders
INNER JOIN custom_message_to_display_on_checkout ON (custom_message_to_display_on_checkout.uid = orders.user_id)
WHERE
orders.delivery_date>custom_message_to_display_on_checkout.date AND orders.status = 4 AND orders.user_id = '203694'
) tmp ON (tmp.user_id = custom_message_to_display_on_checkout.uid)
WHERE
uid = '203694'
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | custom_message_to_display_on_checkout | ref | idx_uid | idx_uid | 4 | const | 1 | 100.00 | Using index | |
| 1 | PRIMARY | <derived2> | ALL | 1 | 100.00 | Using where; Using join buffer (hash join) | |||||
| 2 | DERIVED | custom_message_to_display_on_checkout | ref | idx_uid | idx_uid | 4 | const | 1 | 100.00 | ||
| 2 | DERIVED | orders | ref | idx_delivery_date, idx_user_id, ids_status, orders+delivery_date&status, idx_status_delivery_date_subscription_type, orders_idx_status_droppoint_delivery | idx_user_id | 4 | const | 242 | 16.66 | Using index condition; Using where |