Explain Query (mysql)
EXPLAIN SELECT
extract(year from dd.Calendar_Date) AS period,
dd.Calendar_Date,
IFNULL(stats_contribution.amount,0) AS amount_contribution,
IFNULL(stats_contribution.individuals,0) AS individuals_contribution,
IFNULL(stats_contribution.average_contribution,0) AS average_contribution,
IFNULL(stats_spendings.amount,0) AS amount_spendings,
IFNULL(stats_spendings.individuals,0) AS individuals_spendings
FROM
`Dim_Date` dd
LEFT JOIN (
SELECT
extract(year from created_time) AS period,
SUM(amount) as amount,
COUNT(*) as individuals,
ROUND(SUM(IF(type = 116, ABS(amount), 0 )))/COUNT(transaction_id) AS 'average_contribution'
FROM
`transactions`
WHERE
type IN (116) AND
DATE(created_time) BETWEEN SUBDATE(CURDATE(), INTERVAL 1575 DAY) AND CURDATE() AND
DATE(created_time)>='2020-12-22'
GROUP BY
extract(year from created_time)
) stats_contribution ON (stats_contribution.period = extract(year from dd.Calendar_Date))
LEFT JOIN (
SELECT
extract(year from created_time) AS period,
SUM(amount) as amount,
COUNT(*) as individuals
FROM
`transactions`
WHERE
type IN (112) AND
DATE(created_time) BETWEEN SUBDATE(CURDATE(), INTERVAL 1575 DAY) AND CURDATE() AND
DATE(created_time)>='2020-12-22'
GROUP BY
extract(year from created_time)
) stats_spendings ON (stats_spendings.period = extract(year from dd.Calendar_Date))
WHERE
dd.Calendar_Date BETWEEN SUBDATE(CURDATE(), INTERVAL 1575 DAY) AND CURDATE() AND
DATE(dd.Calendar_Date)>='2020-12-22'
GROUP BY
extract(year from dd.Calendar_Date)
ORDER BY
dd.Calendar_Date ASC
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | dd | range | PRIMARY | PRIMARY | 3 | 2982 | 100.00 | Using where; Using index; Using temporary; Using filesort | ||
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 5 | func | 10 | 100.00 | Using where | |
| 1 | PRIMARY | <derived3> | ref | <auto_key0> | <auto_key0> | 5 | func | 10 | 100.00 | Using where | |
| 3 | DERIVED | transactions | ref | idx_created_time, idx_type, ind_cti_type_create_time | idx_type | 1 | const | 52800 | 100.00 | Using where; Using temporary | |
| 2 | DERIVED | transactions | ref | idx_created_time, idx_type, ind_cti_type_create_time | idx_type | 1 | const | 290490 | 100.00 | Using where; Using temporary |