Как оптимизировать простой sql запрос?

Question

[eSelf]

Не могу оптимизировать запрос.

SELECT AVG(`timestamp`) as 'grouped_timestamp', AVG(`temperature`), MIN(`temperature`), MAX(`temperature`)
FROM rpi_reg_copy WHERE `timestamp` > UNIX_TIMESTAMP(NOW() - INTERVAL 10 year) 
GROUP BY CEIL(`timestamp` / 864000)
ORDER BY 'grouped_timestamp' ASC LIMIT 578

Всего в таблице 4 млн записей. Там хранится время в формате unix timestamp и другие данные, например температура. Интервал между каждой записью 1 минута.

Мне нужно получить средние данные за 10 лет, при этом интервал между каждой точкой должен быть 10 дней (864000 секунд).

Запрос выполняется 8-10 секунд. Причем если меняю GROUP BY CEIL(`timestamp` / 864000) на просто GROUP BY `timestamp`, то запрос выполняется меньше чем за 1 секунду.

Добавлял индекс для поля timestamp, не помогло.

Вот что показывает EXPLAIN:

Answer 1

[Rsa97]

Запрос выполняется 8-10 секунд. Причем если меняю GROUP BY CEIL(`timestamp` / 864000) на просто GROUP BY `timestamp`, то запрос выполняется меньше чем за 1 секунду.

Волне логично. Если во WHERE, GROUP BY, ON и т.п. используется функция от поля, то индекс не используется. В MySQL 5.7.6 и выше можно добавить вычислимое поле с отдельным индексом и делать группировку по нему. В младших версиях - создать отдельное поле и обновлять его триггерами AFTER INSERT/AFTER UPDATE.

Comments

[eSelf]

Спасибо за ответ.
А что делать если интервал (864000) изменяется динамически? Существуют разные интервалы в 2 минуты, час, неделя, месяц и т.д. Для каждого придется создавать вычислимое поле отдельно?

[Rsa97]

eSelf: Да, отдельное поле и отдельный индекс.