I am trying to do a simple per month of year sum of values on a big (>10M) table. However, it faces significant performance issues (7 sec with 4G innodb pool size, in local db with 1G innodb pool size it takes over a minute). The script seems fairly simple.

SELECT   year(date_time),   month(date_time),   sum(value) FROM measurements WHERE   source_id = 2 GROUP BY year(date_time), month(date_time) 

I have tried using MySQL 5.7 and MariaDB 10.2. Using EXPLAIN in both cases provides:

SIMPLE  measurements    ref source_id,source_date   source_id   4   const   4418476 Using where; Using temporary; Using filesort 

It seems I cannot avoid using temporary table. The table has 2 indexes: date_time and source_id, date_time.