Explain Query (mysql)
EXPLAIN SELECT
dd.Calendar_Year,
log_entry.nb_weeks_considered
FROM
Dim_Date dd
INNER JOIN
(
SELECT
year,
max(nb_weeks_considered) as nb_weeks_considered
FROM
securelufacom.supervores_incentive_log sl
INNER JOIN
subscriptions s ON s.user_id = sl.user_id
WHERE
sl.user_id = '308842' AND
sl.created_at >= s.became_superlufavore_on AND
sl.active = 1
GROUP BY
year
) log_entry on dd.Calendar_Year = log_entry.year
GROUP BY
dd.Calendar_Year
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | <derived2> | ALL | 7 | 100.00 | Using temporary | |||||
| 1 | PRIMARY | dd | ALL | 32659 | 10.00 | Using where; Using join buffer (hash join) | |||||
| 2 | DERIVED | s | const | uniq_sub_per_user, idx_user_id | uniq_sub_per_user | 4 | const | 1 | 100.00 | Using temporary | |
| 2 | DERIVED | sl | ref | u_idx_supervores_incentive_log_iso_week_year_user_id, idx_supervores_incentive_log_user_id | idx_supervores_incentive_log_user_id | 4 | const | 230 | 3.33 | Using index condition; Using where |