четвер, 16 липня 2009 р.

MySQL, count и group by

Случается так, что нужно подсчитать количество записей в возвращаемом результате с использованием group by. И вот тут начинается магия. К примеру фреймворк CakePHP (я использую v 1.2) имеет хороший хелпер (helper) для разбиения на страницы (pagination). Он действительно хорошо работает на многих обычных запросах. Если копнуть глубже, то всё, что он делает, это подставляет в запрос LIMIT X предварительно посчитав количество записей в результате. Записи считаются тоже простым добавлением count(*) впереди исходя из чего и рисуются потом ссылки на страницы. Собственно вот она магия с этого момента.



Если запрос имеет в себе group by, то добавленный впереди count(*) не даст желаемого результата. Он не вернёт одно число, как ожидается, но добавится колонка с подсчитанным количеством сгруппированных записей. Мягко говоря не совсем то, что надо.

mysql> select count(*) from tracks where length > 1000;
+----------+
| count(*) |
+----------+
| 50 |
+----------+
1 row in set (0,60 sec)


замечательно, а теперь представим, что запрос был такой:

mysql> select count(*) from tracks where length > 1000 group by user_id;
+----------+
| count(*) |
+----------+
| 5 |
| 3 |
| 2 |
| 3 |
| 3 |
| 6 |
| 3 |
| 2 |
| 19 |
| 2 |
| 2 |
+----------+
11 rows in set (1,67 sec)

Не подходит. Нам нужен результат 11, но CakePHP получит 5, pagination сойдёт с ума и у насбудет только одна страница в пейджере. Если рассмаривать этот пример с параметром вывода по 5 на странице, то на остальные страницы просто не будет ссылок.
Первое, что нужно теперь сделать, это переопределить в модели paginateCount и далее возможны всяческие ухищрения.

Одно из самых простых в плане реализации, это сделать find('all') в этом переопределённом методе и после посчитать количество элементов в массиве посредством count() уже в PHP и возвратить полученное значение. Этот способ можно не проверять. Работает 100%. Но понятно, что тут тратится лишняя память и время. Оно и понятно. Не даром разбивают результаты на страницы, они могут быть очень ёмкими. Оставим этот метод для пионеров и тех, кому лень читать дальше.

Следующее решение - это упростить запрос так, чтобы не использовать group by. Т.е. выбрать из таблицы, в которой поле, по которому проводится группировка является уникальным. К примеру для моего запроса можно сделать так:

mysql> select count(distinct user_id) from users as u join tracks as t on (t.user_id = u.id) where t.length > 1000;
+-------------------------+
| count(distinct user_id) |
+-------------------------+
| 11 |
+-------------------------+
1 row in set (0,66 sec)

И это, наверное, самое ювелирное решение. Результат получен верный. Но для очень сложных запросов может так не получится когда, предположим, нет такой таблицы, где бы хранилось это поле уникально. К тому же требует некоторые человеко/часы. Хотя можно попытаться писать изначальный запрос именно таким образом, но кто же думает наперёд.

Оба указанных варианта могут быть реализованы «по-кейковски» и сохранят абстракцию от БД.

Есть ещё и третий вариант, самый простой:


mysql> select count(*) from (select id from tracks where length > 1000 group by user_id) query;
+----------+
| count(*) |
+----------+
| 11 |
+----------+
1 row in set (1,74 sec)

Но как такое сделать «по-кейковски» я ума не приложу. Так что тут уже ломается абстракция.

3 коментарі:

  1. SELECT count( DISTINCT `user_id` ) FROM tracks WHERE length > 1000

    ВідповістиВидалити
  2. О замене SELECT COUNT можете почитать в моем блоге: http://plutov.by/post/mongodb_counters

    ВідповістиВидалити
    Відповіді
    1. Александр, в статье речь идёт не о том, как избежать COUNT, а о том, что он даёт не те результаты, которые ожидалось, но, как правильно заметил VMM, это решается достаточно просто. Вопрос производительности COUNT тут не затрагивался.

      Видалити

Не обязательно регистрироваться, вы наверняка уже зарегистрированы в одном из сервисов, который предоставляет OpenId, предлагаю воспользоваться им. Подробнее: http://openid.net/what/