Оптимизация запроса where group by

Question

Имею запрос

SELECT DATE_FORMAT(dl.`date_col`, '%Y-%m-%d') AS dateF 
FROM table_name dl 
WHERE dl.`date_col` BETWEEN '2016-01-01' AND '2017-12-01' 
GROUP BY date(dateF) 
ORDER BY dateF;

Время работы 0.735. Как его можно оптимизировать? Пробовал подобный запрос

SELECT DATE_FORMAT(dl.`date_col`, '%Y-%m-%d') as dateF 
FROM table_name dl 
GROUP BY date(dateF) 
HAVING dl.`date_col` BETWEEN '2016-01-01' AND '2017-12-01'
ORDER BY dateF;

Время получилось 0.657, но все таки не хорошо пихать в having, то что должно быть в where. explain-ы для запросов соответственно:

'1', 'SIMPLE', 'dl', 'index', NULL, 'uid', '9', NULL, '767882', 
'Using where; Using index; Using temporary; Using filesort' (where)

'1', 'SIMPLE', 'dl', 'index', NULL, 'uid', '9', NULL, '767883', 
'Using index; Using temporary; Using filesort'  (having)

База MyISAM. Структура таблицы:

'CREATE TABLE `table_name` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_col` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`))

Akina · Answer

Возможное решение по "оптимизации" запроса путём переопределения данных:

-- добавить поле с датой
ALTER TABLE table_name ADD COLUMN only_date_col DATE;
-- пересчитать его значения
UPDATE table_name SET only_date_col = DATE(date_col);
-- проиндексировать поле
CREATE INDEX idx_only_date ON table_name (only_date_col);
-- создать триггеры, обновляющие поле при вставке/изменении данных
CREATE TRIGGER bi_table_name ON table_name BEFORE INSERT 
    FOR EACH ROW SET NEW.only_date_col = DATE(NEW.date_col);
CREATE TRIGGER bu_table_name ON table_name BEFORE UPDATE 
    FOR EACH ROW SET NEW.only_date_col = DATE(NEW.date_col);

Соответственно запрос трансформируется в

SELECT DATE_FORMAT(only_date_col, '%Y-%m-%d') AS dateF, COUNT(*) AS cnt 
FROM table_name
WHERE only_date_col BETWEEN '2016-01-01' AND '2017-12-01' 
GROUP BY only_date_col;

При возникновении подозрений на несогласованность данных (или в процедуре обслуживания БД) - запускать запрос пересчёта значений UPDATE для согласования данных в поле only_date_col.

ВАЖНО! Следует понимать, что данное изменение ухудшит производительность большинства других запросов (если они, как и оптимизируемый запрос, не используют часть даты из поля даты-времени - тогда переписывание их на использование добавленного поля может улучшить их выполнение), а также увеличит расход дискового пространства.

Оптимизация запроса where group by

One Answer

Add your own answers!

Ask a Question