SQL: Подводные камни использования GROUP BY + ORDER BY в одном запросе + авторский ХАК

Недавно, проектируя БД для одного маленького, но достаточно сложного и требовательного по производительности проекта, столкнулся с необходимостью группировать и одновременно сортировать выбранные записи. Однако, сортировка в движке MySQL (как и в других РСУБД) работает уже после формирования массива данных и группировки.

Поясню на примере что имеется ввиду:

Имеем таблицу товаров (id, name, date, cost).

Задача: найти минимальную цену на каждую дату

Запрос, который первым приходит на ум:

SELECT * FROM `test` GROUP BY `date` ORDER BY `cost` ASC;

… выдаст некорректный результат, т.к. GROUP BY работает по принципу DISTINCT: в результирующий набор попадает первая попавшаяся строка, удовлетворяющая условиям. Результат запроса будет следующим:

Строка с ценой = 98 была исключена при группировке. Тут, конечно, можно использовать агрегатную функцию MIN() для выбора в группировке нужной цены. Действительно, в таком случае можно корректно выбрать минимальную цену на каждую дату, но вот id товара будет указан такой же как в первом примере (т.е. мы не сможем сформировать ссылку на товар с минимальной ценой используя MIN() для поиска минимальной цены).

Идем дальше: попробуем решение, которое предлагается в разных вариациях на форумах поддержки СУБД. А именно — SUBSELECT. Смысл решения в том, что сначала мы в подзапросе получаем минимальную цену на каждую дату и после с помощью JOIN используем результаты для выборки товаров с нужными ценами. Пример:

SELECT t.*  FROM test t INNER JOIN (   SELECT `date`, MIN(`cost`) AS min_cost    FROM test    GROUP BY `date` ) dmc  ON t.date = dmc.date AND t.cost = dmc.min_cost ORDER BY t.cost ASC;

В этом случае результат уже будет корректным и можно было бы остановиться:

но…

Во первых, такой запрос сложно составить используя ORM (Doctrine, например, не поддерживает JOIN SUBQUERY, хотя варианты добиться нужного поведения тем не менее сохраняются).
Во вторых, производительность вызывает опасения. А т.к. моя подопытная таблица содержит 1 млн строк, а боевая таблица все 100млн, я решил проверить такой подход на производительность (в тестовом кейсе были использованы 3 WHERE условия и после добавления недостающих индексов вопрос производительности был решен, запрос стал выполняться за 30..40мс).

Т.к. хотелось продолжить работать именно с ORM объектами и не переходить на чистый SQL я пошёл на перекур и вернулся с идеей хака сортировки с помощью индекса:

Идея в том, чтобы использовать индекс, который подсунет в группировку сразу нужное значение минимальной цены.
Пробуем добавить индекс по цене… дает тот же результат. Неудача
Пробуем добавить индекс по дате+цене… Бинго!

ALTER TABLE `test`.`test` ADD INDEX `grpsrt_idx` (`date`, `cost`);

SELECT * FROM `test` GROUP BY `date` ORDER BY `cost` ASC;

Идея работает, осталось проверить производительность и границы применимости (т.к. не забываем, что это все таки хак, а не универсальный метод).

Производительность на той же подопытной (1млн строк) ~50мс, успех!

Границы применимости хака:
1) Тут очень важно сравнивать на совпадение результат полученный вариантом с подзапросом, который дает гарантированный результат, и вариант с хаком. И только убедившись, что полученные хаком данные коректны, запускать их в работу.
2) Использование WHERE foo=bar AND baz=qux GROUP BY date условий работает
В таком случае индекс должен быть построен над полями foo+baz+date+cost.
3) А вот использование WHERE foo IN (bar, baz, qux) уже не работает, т.к. индекс не будет знать минимальной цены сразу для нескольких совпадений. Тут мы в теории должны получить минимальную цену для foo=bar, т.к. это первое подходящее значение, если оно конечно есть в таблице.

Подводя итоге применимости: каждое условие должно точно указывать на единственное значение и это значение должно лежать в индексе.

Выводы:
1. Запрос содержащий GROUP BY и ORDER BY сортирует данные ПОСЛЕ группировки.
Это важно понимать и не допускать таких ошибок.
2. Сортировать по нужному полю можно как минимум двумя способами
— используя MIN-MAX для выборки нужных значений в подзапросе
— и используя наложение индекса, который отсортирует выборку по нужному нам полю, это хак и он не всегда работает, важно это понимать.
3. Зная внутренние принципы работы СУБД можно использовать их в свою пользу с оговоркой: «можно, но очень осторожно!».
4. Рекомендуемым решением является использование подзапроса, т.к. работает хоть и в том же порядке, но все же быстрее и дает гарантию корректного результата.

P.S.: Решить эту задачку с помощью хака индексом мне помогла прочитанная несколькими годами ранее книжка «MySQL. Оптимизация производительности» (Шварц Б., Зайцев П., Ткаченко В. и др.). Крайне рекомендую к прочтению, если производительность важна в ваших проектах.

P.P.S: для использования подзапроса в Doctrine достаточно выбрать нужные ID прямым SQL запросом, а потом получить нужные записи в виде ORM-объектов уже по известным ID, работает также быстро. Но я остановился на решении с индексом.