Write MySQL Queries for time based reporting and table indexing design

This is a simple but specific task for someone who’s very knowledgeable about MySQL time based queries and table design.
The table already exists which stores metrics with a time stamp and some other fields. I already have queries running but need help with optimization of queries and creation of summary/aggregate tables.

The following 3 things are required:
1. Optimize the table including indexing and/or create new summary/aggregate tables (I think hourly time period would work) with query to automatically update summary/aggregate table.
2. Build Query to retrieve time series data (values=count/avg/min/max) from the table in minute, hourly and daily intervals ( with timezone).
3. Build Query to retrieve categories from the table in minute, hourly and daily intervals ( with timezone) – with or without pivot.

The challenge is that the table can have 1 million entries and the query should be able to produce the result as quick as it can. We can create summary/aggregate tables as well – which is also part of this project – if the query itself cannot be made faster.