Правильный count() в MySQL

Рубрика: Development, MySQL | 27 July 2006, 18:37 | Vadim Voituk

Время от времени, читая чужой код, вижу что некоторые разработчики указывают разные параметры в функцию COUNT() для получения количетсва строк.
Например: count(*), count(id) (где id – это primary key), count(0).
Возникает вопрос: “Какой count() правильный? А какие лже-count-ы?”
За основу “правильности” взял скорость выполнения запроса.
Выполнил 3 запроса:

mysql> select count(*) from apache_traffic_logs_current;
mysql> select count(0) from apache_traffic_logs_current;
mysql> select count(id) from apache_traffic_logs_current;


Во всех трех получил время выполнения 0.03 - 0.04 сек. и, как ни странно, один и тот же результат - число 47915774.

Нашел в документации такое:

COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:
mysql> SELECT COUNT(*) FROM student;
This optimization applies only to MyISAM and ISAM tables only, because an exact record count is stored for these table types and can be accessed very quickly. For transactional storage engines (InnoDB, BDB), storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count.

Стоить заметить, что за неимением других, тесты я делал на MyISAM-таблице.

По сути ничего удивительного тут нет, потому идем дальше.
Есть в моей табличке индексное поле
`c_time` datetime NOT NULL default '0000-00-00 00:00:00'

Выполняю эти же 3 запроса но с ограничением на c_time:

SELECT SQL_NO_CACHE count(*)
  FROM apache_traffic_logs_current
  WHERE c_time>='2006-07-25 00:00:00' AND c_time<'2006-07-26 00:00:00';

SELECT SQL_NO_CACHE count(0)
  FROM apache_traffic_logs_current
  WHERE c_time>='2006-07-25 00:00:00' AND c_time<'2006-07-26 00:00:00';

SELECT SQL_NO_CACHE count(id)
  FROM apache_traffic_logs_current
  WHERE c_time>='2006-07-25 00:00:00' AND c_time<'2006-07-26 00:00:00';

В первых двух случаях получаю время выполнения запроса 2.84 - 3.15 сек, а в третем 27.46 - 28.34 (!!!) сек.

Обращаюсь к святая святых всех MySQL-гуру - EXPLAIN запросу и узнаю, что в последнем случае в поле Extra "светится" значение "Using where", в то время как в первых двух запросах Extra показывает "Using where; Using index".

На этой трагической ноте мои тесты закончились.
Интересно ещё взгянуть на подобные результаты в запросах, в каких WHERE выполняется по неключевому полю, а также на не MyISAM-таблицах.

P.S. Кстати, сам, по каким-то неведомым мне причинам, пользуюсь count(*).

Комментариев: 19

19 Responses to “Правильный count() в MySQL”

Комментарии:

  1. A4

    Прикольное исследование!

    Хинт: если отформатируешь запросы, их читабельность вырастет в разы.

  2. vadim

    Спасибо.
    А че их (запросы) форматировать – не такие же они и длинные.

  3. Sect0R

    Заметь, что если используешь индекс при получении кол-ва строк запросом
    SELECT COUNT(`id`) as cnt FROM `users` USE INDEX ( as_id ) WHERE `siteuser` = 1 ‘ . AND `active` = 1′ :
    то время выполнения меньше чем у count(*) и count(0), т.е. при использовании индексов лучше использовать ‘id’
    у меня идекс as_id равен связке столбцов id, siteuser и active.

  4. vadim

    идекс as_id равен связке столбцов id, siteuser и active.

    А зачем id туда сувать?
    Подозреваю что id у тебя primary key, какой по сути является и индексом.

  5. Sect0R

    да, но выборка – то делается не только в примари, но и в siteuser и active

  6. vadim

    но выборка – то делается не только в примари, но и в siteuser и active

    А если у тебя будут делаться выборки по всем комбинациям из 5ти полей – ты будешь 120 индексов со всеми комбинациями полей создавать?

    Что мешает MySQL использовать 2 индекса?
    Первый – primary key (id), второй key (siteuser, active)
    Или даже 3: primary key (id), второй key (siteuser), key(active) ?

    Смысла добавлять id в составной индекс попросту нет: если будет указан id, то MySQL воспользуется primary key и на остальные индексы даже смотреть не будет.
    В случае если в запросе id не указан то твой составной индекс тоже будет как мертвому припарка, т.к. индекса по паре (siteuser, active) у тебя нет, есть только по тройке (id, siteuser, active).

  7. Sect0R

    Вопрос хороший что ему мешает, т.к. он не использует их.
    А сделав составной индекс и указав использовать его я добился изменения скорости запроса с 8-ми секунд до 0,7 сек.
    Есть пища для мозгов.

  8. vadim

    Почему MySQL не использует индекс – это отдельная тема, и к функции COUNT не относится.
    Вполне возможно что оценочное количество строк в результате составляло больше 15-20% от общего количества – в таком случае оптимизатор считает что лучше провести fullscan таблицы чем использовать индексы. Но к иногда сожалению он ошибается.

    P.S. Надеюсь вопрос почему не стоит добавлять primary key в составной индекс уже не стоит?

  9. dkrnl

    спасибо вам, очень помог пост.
    я то наделяся на вменяемость внутренего оптимизатора mysql.
    не много в шоке, век живи век учись. :-|

  10. dkrnl

    EXPLAIN SELECT SQL_NO_CACHE count(0)
    FROM `news`
    WHERE `type`=2

    id|select_type|table|type|possible_keys|key|key_len|ref|rows|extrara
    1|SIMPLE|news|ref|type|type|4|const|8623|Using where;Using index

    а при count(id) имеем только Using where большие лаги.

  11. Vadim Voituk

    Сколько строк всего в таблице?
    Сколько из них подпадает под условие `type`=2?
    Какой тип имеет поле type?
    По нему построен индекс?

    Есть подозрение что при count(0) оптимизатор решает что выгоднее будет выполнить fullscan все таблицы вместо использования индекса (такое бывает когда ожидаемое количество записей превышает 20-25% от общего).
    Почему в данном случае так же не происходит при count(id) – сказать сложно.

  12. geroy

    Тут тоже проводились подобные исследования:
    http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/

  13. Aprel

    проверил на себе.
    Количество записей в таблице: 11 669 902
    время выполнения count(*): 0.01 sec
    время выполнения count(ARL_DISPLAY_NR): 23.02 sec

  14. Anton

    Хм… Тут нюанс еще в том, что count(*) и count(id) в некоторых случаях совсем не одно и то же. Например, если у вас есть left join, то такие count вполне могут дать разные результаты (т.е. id=null в подсчете количества участвовать не будут).

  15. val che

    хай.
    а как посчитать кол-во каждого уникально столбца? select t.name, count(t.name) .. ? t джойнится

  16. dima

    а почему бы не использовать триггеры?

    или счетчики псевдо-кэшировать

    на INNODB count может выполнятся минун 30

  17. максим

    на INNODB count может выполнятся минун 30
    За 30 мин можно вручную посчитать записи:)

    Всегда юзаю count(*) + кэш запросов для countов. Кэш храню в мемори. По скорости заметно быстрее) Но данные не реального времени, ибо мой кэш на целые сутки.

  18. egeshi

    Для COUNT() в скобках можно или ничего не указывать, и тогда учитываются все записи таблицы или сделать выборку, казав в скобках поле, по которому можно отфильтровать записи, и тем самым сделать выборку по определенному значению поля.

  19. Min2win

    Я использую почти всегда COUNT(*). Сколько ни проверял – так всегда быстрее.

Leave a Reply