Comments 10
А не пробовали вариант с отдельной группировкой table2 и джойном к общему запросу после группировки?
ЧатЖПП, как из элементарной задачи на 1 минуту сделать целую статью?)
Программисты, привыкшие писать код, а не тяп-ляп запрос к бд, плачут... Всё-таки эта история, где за оптимизацию запроса отвечает движок БД, здорово ударила по этому направлению, превратив некоторые вещи в магию.
Впору придумывать расширение для SQL – assert для временнОй сложности (в данном случае – O(n1)+O(n2)+O(n3)+O(nr*log(nr)), где n1, n2, n3 – количество строк в исходных таблицах, а nr – в результате).
К сожалению, многие программисты сейчас вообще не пишут запросы к БД, а просто используют orm типа hibernate. Что получается на выходе их мало беспокоит.
Когда человек представляет всю цепочку ORM -> запрос к базе -> обработка запроса (индексы, циклы) хотя бы в первом приближении (до способности увидеть, что тут вылезает 30*500*20 операций на юзера на ровном месте, а в идеале хочется 30+500+20) – это здорово помогает при написании на любом из уровней.
Гм, начнём с вопроса, а что у вас считает count(*)?
В исходном запросе оно у вас посчитает для каждого юзера количество строк во всех связанных таблицах, 30 x 500 x 20 = 300000. Да и соответственно прочие перечисленные агрегатные функции считают среднее / максимум среди этих 300 тыс строк. Для максимума результат от этого не меняется, а вот для среднего, если в реальных данных будет не строго по 30 / 500 / 20 записей на юзера, а колеблющееся количество - среднее значение будет меняться в зависимости от количества соответствующих строк в декартовом произведении (table1 x table2 x table3 where userid = u.id).
В конечном запросе в колонке count для каждого юзера будет 1, и агрегатная функция max будет искать максимум только среди 500 соответствующих строк. Для максимума не важно, но для других агрегатных функций это не эквивалентная замена.
Т.е. в данном случае имеет место не оптимизация, а замена запроса на другой, возвращающий, вообще говоря, другие результаты. Какой из них в вашем приложении более правилен - ещё вопрос.
Да, Вы правы. Спасибо.
count остался от прошлого показа этого примера "на пальцах", чтобы без чтения плана показать проблему. Если смогу отредактировать статью - уберу его из примера.
Допустим, у нас в t1 3 строки, в t2 4 строки, в t3 2 строки (значения 1 и 3).
Очевидно, avg(t3.value) = 2. Но если мы эти строки прогоним через декартово, то у нас получится 12(3 строки из t1 * 4 строки из t2) строк для 1 и 12 строк для 3, avg(t3.value) в данном случае будет (12*1+12*3)/24 = 2.
Если мы добавим 1 строку в t1 и 1 строку в t2, то после джоинов у нас станет по 20 строк на каждое значение из t3 avg(t3.value) = (20*1+20*3)/40 = 2.
Попробуем добавить значение в t3, допустим 5, тогда среднее должно быть равно 3.
Проверяем: (20*1+20*3+20*5)/60=180/60=3.
Среднее не меняется.
Когда JOIN тянет ко дну: как одно изменение ускорило запрос в 75 раз