Группировка по дате в MySQL

Рейтинг: 1Ответов: 1Опубликовано: 17.09.2014

Здравствуйте. Имеется таблица с большим количеством записей. В ней поле datetime c типом TIMESTAMP. Требуется группировка по датам. У меня появилось 3 варианта.

  1. GROUP BY DATE(datetime)
  2. GROUP BY UNIX\_TIMESTAMP(datetime) - UNIX\_TIMESTAMP(datetime)%(60\*60\*24) - отнимаем остаток от деления на день, то есть округляем до дня. Первый вариант работает ~2.1 сек, второй ~0.95.

Тогда я понял, что с целым типом мускулю легче работать, и добавил к таблице поле unix\_datetime с типом INT, и записал туда UNIX\_TIMESTAMP(datetime), чтоб избавиться от преобразований.
И сделал 3-ю группировку: GROUP BY unix\_datetime - unix\_datetime%(60\*60\*24)
К моему удивлению, она работает за ~1.25 сек.
Индексы есть и на datetime и на unix_datetime.

Итого имеем результаты выполнения:

  1. GROUP BY DATE(datetime) - ~2.1 сек
  2. GROUP BY UNIX\_TIMESTAMP(datetime) - UNIX\_TIMESTAMP(datetime)%(60\*60\*24) - ~0.95 сек.
  3. GROUP BY unix\_datetime - unix\_datetime%(60\*60\*24) - ~1.25 сек.

Кто-нибудь может объяснить, почему второй вариант работает быстрее 3-го? Может кто предложит еще варианты (группировать требуется не только по дате, но и по часам, неделям, месяцам). Просто тестил на группировке по дням.

sql mysql
Источник: Stack Overflow на русском

Ответы

▲ 1

Сложно сказать, почему такая разница между INT и TIMESTAMP в пользу второго. По идее, все должно быть наоборот, потому как TIMESTAMP на низком уровне в MySQL хранится как целое число. Видимо, действительно что-то в алгоритмах...

Но одно абсолютно точно: индексы у вас хоть и имеются, но в этом запросе они не используются. Это потому, что вы используете не чистое значение, а выражение по нему. Для наиболее эффективного выполнения этого запроса стоит пойти на денормализацию. Добавить поле с типом DATE, заполнить и проиндексировать его, чтобы группировка выполнялась по полю без выражений.