Please provide SHOW CREATE TABLE for the fact and summary tables. (Your brief description leaves out too many subtle details.) There are 'right' ways and 'wrong' ways to do indexes on PARTITIONed tables.
SELECT main.device_key, main.component_key,
TO_DAYS(main.fct_ts),
CONVERT(TRUNCATE(main.fct_ts, -4), DATETIME) AS tick,
AVG(main.fct_value) AS fct_avg,
MIN(main.fct_value) AS fct_min,
MAX(main.fct_value) AS fct_max,
COUNT(main.fct_value) AS fct_count
FROM raw_data main
WHERE fct_ts >= TIMESTAMP('2013-09-01 00:00:00')
AND fct_ts < TIMESTAMP('2013-09-02 00:00:00')
GROUP BY fct_ts, device_key, component_key,
tick
begs for an index starting with fct_ts. Or correct partitioning on fct_ts.
> The raw_data table is partition by date
Need more details. The only flavor of this that will work is something like
PARTITION BY RANGE (TO_DAYS(dt)) (
start VALUES LESS THAN (0),
from20120315 VALUES LESS THAN (TO_DAYS('2012-03-16')),
from20120316 VALUES LESS THAN (TO_DAYS('2012-03-17')),
...
from20120414 VALUES LESS THAN (TO_DAYS('2012-04-15')),
from20120415 VALUES LESS THAN (TO_DAYS('2012-04-16')),
future VALUES LESS THAN MAXVALUE
What is the datatype of fct_ts? Older versions of MySQL require it to be DATETIME. Newer versions allow TIMESTAMP; the syntax is slightly different. What version are you using?
TIMESTAMP('2013-09-01 00:00:00') does not need the TIMESTAMP() function; the string is effectively a timestamp.
Further discussion:
http://mysql.rjweb.org/doc.php/partitionmaint
http://mysql.rjweb.org/doc.php/datawarehouse