Заметки, Проекты → Простая SQL-задача

Время от времени приходится решать не тривиальные задачи. Одна из последних: получить даты, когда были какие-либо записи. В день может быть 84600 записей на пользователя (по количеству секунд), соответственно в месяц около 2 600 000 на пользователя. Пользователей — не ограничено (тестировалось на базе в пару сотен пользователей). Индексы проставлены.

Казалось-бы, все элементарно и просто:

SELECT
	DATE_FORMAT(`date`, '%Y-%m-%d') as `fdate`
FROM
	`user_coord`
WHERE
	`user` = '4' AND
	`date` > '2013-09' AND
	`date` < '2013-10'
GROUP BY
	`fdate`

Но, мы же помним, что GROUP BY = зло? Да и время выполнения запроса, 1.5 — 2 секунды, совсем не устраивает. Включаем мозг…

Итак… Ясно, что от GROUP BY необходимо избавляться. EXPLAIN подсказывает, что затрагивается примерно 60 000 000 записей, что явно не гуд. Ок. Варинаты? Первое, что приходит на ум, UNION. Логично предположить, что 30 простых запросов будут быстрее одно с GROUP BY. Сразу стало понятно, что мнение сильно ошибочное, но для новичков приведу код:

SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-01" UNION
SELECT 2 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-02" UNION
SELECT 3 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-03" UNION
SELECT 4 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-04" UNION
SELECT 5 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-05" UNION
SELECT 6 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-06" UNION
SELECT 7 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-07" UNION
SELECT 8 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-08" UNION
SELECT 9 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-09" UNION
SELECT 10 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-10" UNION
SELECT 11 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-11" UNION
SELECT 12 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-12" UNION
SELECT 13 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-13" UNION
SELECT 14 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-14" UNION
SELECT 15 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-15" UNION
SELECT 16 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-16" UNION
SELECT 17 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-17" UNION
SELECT 18 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-18" UNION
SELECT 19 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-19" UNION
SELECT 20 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-20" UNION
SELECT 21 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-21" UNION
SELECT 22 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-22" UNION
SELECT 23 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-23" UNION
SELECT 24 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-24" UNION
SELECT 25 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-25" UNION
SELECT 26 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-26" UNION
SELECT 27 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-27" UNION
SELECT 28 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-28" UNION
SELECT 29 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-29" UNION
SELECT 30 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-30" UNION
SELECT 31 FROM `user_coord` WHERE `user` = 4 AND `date` = "2013-09-31"

Те, кто разбирается в SQL, уже увидели ошибку. Для остальных объясню: `date` = «2013-09-01» — совсем не то же самое, что `date` > ‘2013-09-01’ AND `date` < '2013-09-02'. Есть еще часы, минуты и секунда. Получается, что `date` = "2013-09-01" == `date` = "2013-09-01 00:00:00". Записи в это время может и не быть, но может быть в 2013-09-01 00:00:01, например. Соответственно, результат не верный. Думаем дальше. Что нам реально необходимо от запроса и что нам реально мешает? Необходимо - дата, когда были данные. Именно дата, а не время. Мешает - большая куча данных в работе запроса. Подумали? Ну тогда дальше становится довольно просто: 1. Нужна не сама дата, а флаг, были ли данные за конкретную дату. 2. Уменьшение данныех в запросе делается парой способов: LIMIT и GROUP BY. Второе - зло, остается первое. НО! LIMIT не может быть использован в UNION, JOIN и пр. Посмотрев правильный, но медленный запрос и не правильный, но быстрый, проанализировав и подумав, находим простое и быстрое решение: 30 (по количетсву дней в месяце) запросов в пределах одного ("эмулируем") UNION:

SELECT
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-01" AND `date` < "2013-09-02" LIMIT 1) AS `1`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-02" AND `date` < "2013-09-03" LIMIT 1) AS `2`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-03" AND `date` < "2013-09-04" LIMIT 1) AS `3`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-04" AND `date` < "2013-09-05" LIMIT 1) AS `4`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-05" AND `date` < "2013-09-06" LIMIT 1) AS `5`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-06" AND `date` < "2013-09-07"  LIMIT 1) AS `6`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-07" AND `date` < "2013-09-08"  LIMIT 1) AS `7`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-08" AND `date` < "2013-09-09"  LIMIT 1) AS `8`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-09" AND `date` < "2013-09-10"  LIMIT 1) AS `9`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-10" AND `date` < "2013-09-11"  LIMIT 1) AS `10`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-11" AND `date` < "2013-09-12"  LIMIT 1) AS `11`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-12" AND `date` < "2013-09-13"  LIMIT 1) AS `12`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-13" AND `date` < "2013-09-14"  LIMIT 1) AS `13`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-14" AND `date` < "2013-09-15"  LIMIT 1) AS `14`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-15" AND `date` < "2013-09-16"  LIMIT 1) AS `15`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-16" AND `date` < "2013-09-17"  LIMIT 1) AS `16`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-17" AND `date` < "2013-09-18"  LIMIT 1) AS `17`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-18" AND `date` < "2013-09-19"  LIMIT 1) AS `18`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-19" AND `date` < "2013-09-20"  LIMIT 1) AS `19`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-20" AND `date` < "2013-09-21"  LIMIT 1) AS `20`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-21" AND `date` < "2013-09-22"  LIMIT 1) AS `21`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-22" AND `date` < "2013-09-23"  LIMIT 1) AS `22`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-23" AND `date` < "2013-09-24"  LIMIT 1) AS `23`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-24" AND `date` < "2013-09-25"  LIMIT 1) AS `24`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-25" AND `date` < "2013-09-26"  LIMIT 1) AS `25`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-26" AND `date` < "2013-09-27"  LIMIT 1) AS `26`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-27" AND `date` < "2013-09-28"  LIMIT 1) AS `27`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-28" AND `date` < "2013-09-29"  LIMIT 1) AS `28`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-29" AND `date` < "2013-09-30"  LIMIT 1) AS `29`,
	(SELECT 1 FROM `user_coord` WHERE `user` = 4 AND `date` > "2013-09-30" AND `date` < "2013-10-01"  LIMIT 1) AS `30`
LIMIT
	1

Итог запроса: 30 строк в работе и 8 мс выполнения. Сравним с 1.5-2 секунды изначально?)
Быстрее вариантов придумать не смог (честно — пытался еще минут 20). Есть в природе такие варианты?

P.S.: отдельно, для любителей ORM: помогите составить последний запрос, используя ORM. Нет, это не шутка. Всегда было интересно, как подобное решается с использованием Object-relational mapping. Все, с кем общался на данное время на данную тему, предлагают одно и то же — отдельную таблицу + сохранение в нее подготовленных данных (INSERT IGNORE или SELECT + INSERT). Отметаю сразу — грузить базу IGNORE-ами — бред. Делать ежесекундно по дополнительному SLECT — не лучше. Неужели, используя ORM нельяз сделать довольно простую вещь?