Подсчёт общего количества строк выборке в mySQL при использовании LIMIT

    Один хороший человек хочет попасть на хабр. Для подтверждения своих благих намерений он написал статью, которую я привожу вам.

    Наверняка многие знают о существовании в mySQL функции FOUND_ROWS(). Её чаще всего используют для подсчета общего числа строк в таблице, удовлетворяющих заданным условиям. Используют её обычно совместно с директивой SQL_CALC_FOUND_ROWS следующим образом:

    Mysql> SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE column > 1 LIMIT 0, 50;
    Mysql> SELECT FOUND_ROWS();

    Результатом второго запроса будет общее количество строк в таблице «table», удовлетворяющих условию «column > 1» (а не только тех, что были возвращены первым запросом).
    Следует учитывать некоторые особенности этой функции, когда используете её без директивы SQL_CALC_FOUND_ROWS, о чём добросовестно предупреждает документация mySQL: в этом случае она вернёт общее количество обработанных строк (не возвращённых!). Например:

    Mysql> SELECT * FROM table LIMIT 0, 50;
    Mysql> SELECT FOUND_ROWS();

    Результатом, как и ожидается, будет «50». Но следующие запросы вернут не слишком ожидаемый результат:

    Mysql> SELECT * FROM table WHERE column > 1 LIMIT 50, 50;
    Mysql> SELECT FOUND_ROWS();

    Несмотря на то, что первый запрос вернёт 50 строк, результатом будет «100», т.к. mySQL пришлось просмотреть именно 100 строк.

    Кроме того, при использовании UNION с SQL_CALC_FOUND_ROWS также появляются некоторые специфические требования:
    • директива SQL_CALC_FOUND_ROWS должна появляться единственный раз и только в первом запросе;
    • точные результаты возвращаются только при использовании UNION ALL, т.к. при использовании UNION без ALL некоторые строки будут удалены, что не учитывается функцией FOUND_ROWS();
    • если не используется глобальный LIMIT, то директива SQL_CALC_FOUND_ROWS без проигнорирована.

    Документация mySQL прямо рекомендует использовать данную функцию для подсчёта общего количества строк в таблице, удовлетворяющих параметрам запроса (например, при разбивки какого-либо списка на страницы), утверждая, что это быстрее, чем два классически используемых запроса вроде:

    Mysql> SELECT * FROM table WHERE column > 1 LIMIT 50, 50;
    Mysql> SELECT COUNT(*) FROM table WHERE column > 1;

    Вопрос: если вариант с использованием функции FOUND_ROWS() действительно быстрее, то почему его не используют повсеместно? Я решил сравнить оба подхода. Для этого была создана таблица:
    CREATE TABLE `table_1` (                  
          `id` int(10) unsigned NOT NULL auto_increment,      
          `column_1` varchar(32) default NULL,           
          `column_2` varchar(32) default NULL,           
          PRIMARY KEY (`id`),                   
          KEY `column_2` (`column_2`)               
         ) ENGINE=MyISAM AUTO_INCREMENT=1;

    * This source code was highlighted with Source Code Highlighter.


    Таблица содержит три колонки: числовое поле id с auto_increment и PRIMARY-ключом; текстовую колонку column_1 без индекса и текстовую же колонку с индексом column_2. Таблица была заполнена с помощью следующего PHP-скрипта:

    <?php
    for($i = 0; $i < 457128; $i ++) {
      $db->insert ( 'table_1', array ('column_1', 'column_2' ), array ( md5(uniqid(rand(), true)), md5(uniqid(rand(), true)) ) );
    }
    ?>

    * This source code was highlighted with Source Code Highlighter.


    $db – это обёртка над PHP-функциями по работе с mySQL. В ней в частности проводится замер времени исполнения запросов к БД.
    Далее производились следующие виды выборок:
    • «Стандартный» запрос c выборкой по PRIMARY-ключу
      SELECT * FROM `table_1` WHERE `id` < $id_limit LIMIT 1000
      SELECT COUNT(*) FROM `table_1` WHERE `id` < $id_limit
    • SQL_CALC_FOUND_ROWS c выборкой по PRIMARY ключу:
      SELECT SQL_CALC_FOUND_ROWS * FROM `table_1` WHERE `id` < $id_limit LIMIT 1000
      SELECT FOUND_ROWS() AS count
    • «Стандартный» запрос c выборкой по колонке БЕЗ КЛЮЧА
      SELECT * FROM `table_1` WHERE `column_1` LIKE '$column_1_limit%' LIMIT 1000
      SELECT COUNT(*) FROM `table_1` WHERE `column_1` LIKE '$column_1_limit%'
    • SQL_CALC_FOUND_ROWS c выборкой по колонке БЕЗ КЛЮЧА
      SELECT SQL_CALC_FOUND_ROWS * FROM `table_1` WHERE `column_1` LIKE '$column_1_limit%' LIMIT 1000
      SELECT FOUND_ROWS() AS count
    • «Стандартный» запрос c выборкой по колонке VARCHAR с КЛЮЧОМ
      SELECT * FROM `table_1` WHERE `column_2` LIKE '$column_2_limit%' LIMIT 1000
      SELECT COUNT(*) FROM `table_1` WHERE `column_2` LIKE '$column_2_limit%'
    • SQL_CALC_FOUND_ROWS c выборкой по колонке БЕЗ КЛЮЧА
      SELECT SQL_CALC_FOUND_ROWS * FROM `table_1` WHERE `column_2` LIKE '$column_2_limit%' LIMIT 1000
      SELECT FOUND_ROWS() AS count
    • «Стандартный» запрос c выборкой по обеим колонкам
      SELECT * FROM `table_1` WHERE `column_1` LIKE '$column_1_limit_2%' AND `column_2` LIKE '$column_2_limit_2%' LIMIT 1000
      SELECT COUNT(*) FROM `table_1` WHERE `column_1` LIKE '$column_1_limit_2%' AND `column_2` LIKE '$column_2_limit_2%'
    • SQL_CALC_FOUND_ROWS c выборкой по обеим колонкам
      SELECT SQL_CALC_FOUND_ROWS * FROM `table_1` WHERE `column_1` LIKE '$column_1_limit_2%' AND `column_2` LIKE '$column_2_limit_2%' LIMIT 1000
      SELECT FOUND_ROWS() AS count
    • «Стандартный» запрос c выборкой по обеим колонкам + PRIMARY
      SELECT * FROM `table_1` WHERE `column_1` LIKE '$column_1_limit_3%' AND `column_2` LIKE '$column_2_limit_3%' AND `id` < $id_limit_2 LIMIT 1000
      SELECT COUNT(*) FROM `table_1` WHERE `column_1` LIKE '$column_1_limit_3%' AND `column_2` LIKE '$column_2_limit_3%' AND `id` < $id_limit_2
    • SQL_CALC_FOUND_ROWS c выборкой по обеим колонкам + PRIMARY
      SELECT SQL_CALC_FOUND_ROWS * FROM `table_1` WHERE `column_1` LIKE '$column_1_limit_3%' AND `column_2` LIKE '$column_2_limit_3%' AND `id` < $id_limit_2 LIMIT 1000
      SELECT FOUND_ROWS() AS count


    Каждый из запросов исполнялся 10 раз с разными параметрами, при этом параметры у «Стандартного» и SQL_CALC_FOUND_ROWS запроса одного типа на одной итерации – одинаковые. Т.е.:

    for($i = 0; $i < 10; $i ++) {
      $id_limit = rand(15000, 20000);
      $id_limit_2 = rand(15000, 20000);

      $column_1_limit = rand(1, 9);
      $column_2_limit = rand(1, 9);

      $column_1_limit_2 = rand(10, 20);
      $column_2_limit_2 = rand(10, 20);

      $column_1_limit_3 = rand(20, 30);
      $column_2_limit_3 = rand(20, 30);
      // НИЖЕ – ИСПОЛНЕНИЕ ЗАПРОСОВ
    }

    * This source code was highlighted with Source Code Highlighter.


    Результаты тестирования можно увидеть на диаграмме:

    image

    Получается, что всё не так однозначно, как говориться в документации mySQL. При использовании выборки по колонкам с индексам однозначно быстрее «классическая» схема. При использовании же колонок без индексов, а также смешанных запросов, быстрее становится функция FOUND_ROWS(), однако её выигрыш весьма незначителен.
    Таким образом, выбор подхода – дело каждого конкретного случая. Когда производительность не имеет особого значения и важны удобство и скорость написания кода, то вполне можно использовать FOUND_ROWS() + SQL_CALC_FOUND_ROWS. В противном случае, видимо, предпочтительнее использовать классическую схему из двух запросов.
    Тестирование проводилось на двух машинах:
    • Windows XP SP3, Intel Pentium Core 2 Duo E8300 @ 2.83 GHz, 2 GB, mySQL 5.0.51a
    • Ubuntu 8.04, AMD Opteron 2344 HE Quad-Core, 4 GB, 5.0.51a-3ubuntu5.4

    Диаграмма показывает результаты в Windows XP. Результаты (не время выборок) для обеих систем одинаковые.
    Поделиться публикацией
    Комментарии 79
      0
      Прошу прощения за «чайницкий» вопрос, но зачем в «двух классически используемых запросах» первый запрос? Разве второй не возвращает искомое количество строк, удовлетворяющих условию?
        +1
        Он нужен для «пагинатора». COUNT(*) вычисляет общее количество строк удовлетворяющих условию (всего страниц, например), а первый достает некоторый кусочек этих данных (выбранная страница, в данном случае 2-я).
          +5
          Спасибо, понял.

          Просто я всегда делал в обратном порядке:
          — запрашивал число строк через COUNT(*)
          — вычислял число страниц
          — проверял ввод пользователя (а то вдруг кто запросит страницу 100, а их всего две :)
          — выбирал нужные строки LIMIT'ом
            0
            Я тоже. Думаю здесь так написано просто для примера
              –3
              ехх, запрашивать надо не страницу, а оффсет =)
          +3
          картинок не видно у меня
            0
            Она всего одна. На гугловском Picasa Web Albums. Должно работать хорошо.
              0
              У вас расширение картинки указано GIF, а на сервере лежит расширение gif

                0
                Наоборот
                  0
                  это я поменял уже
              0
              аналогично картинку не вижу
                0
                странно, если открыть картинку в отдельном окне, то она показывается
                может так кто увидит yfrog.com/7ddiagrammag
              +1
              >>Mysql> SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE column > 1 LIMIT 50, 50;
              >>Mysql> SELECT FOUND_ROWS();

              >>Несмотря на то, что первый запрос вернёт 50 строк, результатом будет «100», т.к. mySQL пришлось просмотреть именно 100 строк.

              Если я не ошибаюсь в этом примере SQL_CALC_FOUND_ROWS лишний, так как с ним FOUND_ROWS вернёт всё-таки число строк в таблице удовлетворяющих условию, а не количество просмотренных строк.
                0
                да, действительно. пофиксил
                  0
                  Да, тут что-то не то
                  –17
                  статья интересная, однако большой минус автору использующему даже в примерах select count(*) — помните — использовать в запросах * это как минимум медленно, как максимум плохой тон.

                  всегда пишите поля которые вам нужно выбрать.
                    0
                    я читал обратное.
                    не подкрепите свои слова ссылкой?
                      –2
                      я не могу отсканить mysql traning course к сожалению
                        –2
                        кстати покажите и вы обратное утверждение
                          0
                          я не пытался с Вами спорить.
                          ниже Вы сами все уже ответили.

                          да, в топике речь про myisam и звездочка рассматривается только в контексте count().
                          +2
                          проблема характерна для таблиц типа InnoDB. В них действительно писать COUNT(*) ни в коем случае нельзя из-за низкой скорости выполнения таких запросов.
                          для таблиц типа MyISAM такие запросы не страшны.
                            +1
                            в innodb проблема не в count(*), а в том, что в метаданных таблицы не хранится информации о числе записей. так что это применимо только к запросу SELECT COUNT(*) FROM `table`;
                          +5
                          и правда странное утверждение.
                          как сказано в мане: для таблиц HEAP и MyISAM функция COUNT(*), которая вызывается для одной таблицы и не содержит предложения WHERE, берется непосредственно из табличной информации.
                            0
                            maisonbisson.com/blog/post/11796/easy-mysql-performance-tips/
                            вот почитайте и сходите в гугл по запросу mysql count(*) perfomance
                              +4
                              то-есть я должен верить какому-то сомнительному блогу, а не ману? :)
                                –4
                                выбор исключительно за вами чему верить
                            0
                            для COUNT это не имеет значения, хотя я, обычно пишу COUNT(id), где id — PK
                              +4
                              count(*) работает гораздо быстрее, чем count(field)
                                0
                                Где про это почитать?
                                +1
                                А чего минусовать-то? Проверяйте не на бестолковом запросе, типа «select count(*) from table» а на реальном с WHERE — разница в разы.
                                  0
                                  все верно.
                                  в pgsql тоже так.
                                  если указывать (*) то база сама найдет поле с лучшим для нее индексом для подсчета.
                                  если указывать конкретно поле, то это может стать не лучшим выбором для скорости.
                                  все можно проверить тестами.
                                +5
                                Читаем внимательно мануал по COUNT.
                                Звездочка в COUNT и звездочка в списке полей для выборки ничего общего не имеют.
                                  –3
                                  ага, только это относится к майисам, даю 100 в гору что привычка превыше всего и автор пишет и в иннодб так что замедлит запрос. так что мыслить шире надо, а не только применять все к myisam
                                    0
                                    это относится только к 1 типу запросов — запросы без WHERE.
                                      –1
                                      да что за чушь.

                                      count(*) всегда быстрее чем count(expr). Ибо count(expr) должен применить expr на каждый ряд и вывести то, где не получилось в итоге null. А count(*) возвращает явно число вернувшихся рядов и все. Другими словами:

                                      select count(*) from table where mycol is not null
                                      select count(my_col) from table

                                      абсолютно одинаковые. А значит делать count(id) это равнозначно дописыванию ненужного where id is not NULL в запрос. Оптимизатор это схавает, но не всегда.

                                      в MyISAM у метаифы в таблице вообще явно записано число рядов. Следовательно, простой запрос select count(*) from table можно вообще не исполнять — а просто выплюнуть это число. В innodb не прокатит, т.к. в 1 момнет времени кол-во рядов в таблице может быть разным в разных транзакциях.

                                      — а вы, фантазёры, блин.
                                        0
                                        перечитай, пожалуйста, комментарий, на который я отвечал. я даже выделю тебе важную часть:
                                        >> ага, только это относится к майисам, даю 100 в гору что привычка превыше всего и автор пишет и в иннодб так что замедлит запрос.

                                        мой ответ:
                                        >> это относится только к 1 типу запросов — запросы без WHERE.

                                        я говорил только о SELECT COUNT(*) FROM `table` применительно к озвученным storage. ты понимаешь, что я ответил то же самое, что и ты?
                                        habrahabr.ru/blogs/mysql/64655/#comment_1802657

                                        теперь можешь выдыхать.
                                          0
                                          ррр, ну значит цитировать надо с врапом в 72 символа, а то непонятно иногда ;) Пардон.
                                  +3
                                  select * from и select count(*) from — это две большие разницы.
                                  Более того, select count(*) from и select count(field) from вернут разные результаты, если в поле field есть стоки с NULL.
                                    –6
                                    так считать то надо с умом, а не тупо любое поле.
                                      –2
                                      Мне ли этого не знать…
                                        +2
                                        учите матчасть!!!
                                        count(*) — это вовсе не значит «считать любое поле».

                                        ну ппц. на ком инет держится)
                                      +1
                                      count(*) выберет количество найденных записей, не выбирая конкретные значения.
                                      count(id) не только выберет записи, но и будет анализировать id, что дольше.
                                        0
                                        поэтому я соблюдаю нейтралитет и всегда пишу COUNT(1)
                                          0
                                          если бы оптимизатор мускуля был бы совснем тупой, то он-таки делал бы внутренний nonnull(1) на твою единичку =)) А хз — может и делает.
                                        +2
                                        Таблица INNODB, тест на рабочем сервере

                                        SELECT SQL_NO_CACHE count( * )
                                        FROM song

                                        (~3,329,071 всего, запрос занял 0.4882 сек.)

                                        SELECT SQL_NO_CACHE count( song_id )
                                        FROM song

                                        (~3,329,071 всего, запрос занял 0.4811 сек.)

                                        разница минимальна, Explain показвает практически одно и то-же.
                                          –6
                                          искренне рад, только вы нагрузите базу эдак на 1.5к запросов в секунду разнообразных и потом выполняйте вот такие подсчеты, вам эти микросекунды в сумму покажутся вечностью
                                            0
                                            Я же написал, сервер рабочий, под нагрузкой. Тем более что обычно count кешируется либо на уровне html, либо в memcaсhed с expire.
                                            0
                                            Повторите тоже самое с уловием в WHERE типа «song_lenght > бла-бла-бла» или что-то типа того.
                                              0
                                              SELECT SQL_NO_CACHE count( song_id )
                                              FROM song WHERE song_added = '2009-07-09'
                                              запрос занял 0.0539 сек
                                              результат 100714

                                              SELECT SQL_NO_CACHE count( * )
                                              FROM song WHERE song_added = '2009-07-09'
                                              запрос занял 0.0512 сек.)
                                              результат 100714

                                              song_added естественно index
                                              Explain одинаков

                                              Думаю разница в пределах погрешности, ибо время выполнения запроса каждый раз прыгало на +-40мс

                                                0
                                                А вы RANGE запросы попробуйте, BETWEEN воткните по song_added ;-)
                                                  +14
                                                  В исполком пришла жалоба:
                                                  «Напротив моего окна женская баня. Мне все видно и это отвлекает меня и вообще действует на мой моральный облик. Прошу предоставить мне новую квартиру».
                                                  Приехала комиссия, смотрят в окно.
                                                  — Ну и что? Ничего не видно!
                                                  — А вы на шкаф залезьте!
                                                  — Ну, залез, — говорит представитель, — все равно не видно!
                                                  — Двигайтесь левее…
                                                  — Все равно не видно!
                                                  — Еще левее!
                                                  Тут представитель двигается и падает с края шкафа.
                                                  — Вот видите! А я так целый день!
                                              0
                                              а теперь давайте наконец включим голову и подумаем как сделать базе то, что вы от неё хотите.

                                              1) count(*) (если без where и мы юзаем innodb) — то это просто вытащить длину любого ключа. Как правило — primary key. В queryplan будет type=index
                                              2) count(col) заставит смотреть col is not null на каждом ряде. Если col проиндексирована, то результат будет почти такой же как и в 1 пункте, с той лишь разницей что тут тока btree индекс прокатит. Если же индекса нету — то будет *опа.

                                              И ещё очень умно писать SQL_NO_CACHE там где кеша в принципе быть не может =).
                                              0
                                              Ну вот здесь www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/ пишут что для myisam count(*) всё же предпочтительней, и, чего я раньше не знал, COUNT(*) при возможности будет использовать подходящий индекс.
                                                0
                                                Для count(*) полностью наоборот.
                                                +1
                                                субъективность данного теста по вопросом, так как не была использована деректива SQL_NO_CACHE
                                                  +1
                                                  вы имели в виду объективность?
                                                    +1
                                                    да, опечатка, извините, это наверное знак что пора идти домой :)
                                                    0
                                                    Для того, чтобы ничего не кэшировалось каждую итерацию используются новые параметры. В чём Вы видите здесь потенциальный кэш?
                                                    0
                                                    Когда производительность не имеет особого значения и важны удобство и скорость написания кода, то вполне можно использовать FOUND_ROWS() + SQL_CALC_FOUND_ROWS

                                                    Я что-то не понял, FOUND_ROWS() и SQL_CALC_FOUND_ROWS удобнее и быстрее чем COUNT() пишутся? Ну допустим. Поднимите, пожалуйста, руки, кто после этого поста сразу станет использовать FOUND_ROWS() + SQL_CALC_FOUND_ROWS.
                                                      0
                                                      у меня дежавю, но где-то недавно я уже про это читал, правда, на английском.
                                                      0
                                                      Для замеров времени выполнения запросов я бы прекомендовал использовать встроенное профилирование запросов в mySQL + можно получить дополнительную информацию о количестве оперативной памяти, проца и тд
                                                      dev.mysql.com/doc/refman/5.1/en/show-profiles.html
                                                        0
                                                        Как поведет себя SQL_CALC_FOUND_ROWS если в запросе появится JOIN?
                                                          0
                                                          какая разница, он оперирует выборкой.
                                                          0
                                                          Как раз встроенное профилирование и использовалось.
                                                            0
                                                            >> $db – это обёртка над PHP-функциями по работе с mySQL. В ней в частности проводится замер времени исполнения запросов к БД.

                                                            думаю использовался функционал класса. мне просто интересна была статистика по использованному процессорного времени и размера оперативной памяти.
                                                          0
                                                          Вообще, я категорически не советую на таблицах с тысячами строк использовать пагинатор на основе LIMIT, он ж будет тупо перебирать все записи.
                                                            0
                                                            это если юзеры будут ходить на последние таблицы.
                                                            ps: LIMIT очень медленный, когда размер строки динамический. используйте FIXED ROWS и тогда смещение на произвольную строку будет вычисляться практически мгновенно.
                                                              0
                                                              простите за некропостинг =) но можно про FIXED ROWS подробнее? А то как-то гугол совсем падает при таком запросе =)
                                                                0
                                                                Это актуально для Myisam только, я тогда ошибался :-)
                                                            0
                                                            Ради интереса, в постгресе:

                                                            1. Со астериском
                                                            EXPLAIN ANALYZE SELECT COUNT(*) FROM test;
                                                            Aggregate (cost=173166.50..173166.51 rows=1 width=0) (actual time=22994.037..22994.038 rows=1 loops=1)
                                                            -> Seq Scan on log (cost=0.00..164354.60 rows=3524760 width=0) (actual time=9361.992..21308.870 rows=3568867 loops=1)
                                                            Total runtime: 23017.941 ms
                                                            2. По primary key
                                                            EXPLAIN ANALYZE SELECT COUNT(test_id) FROM test;
                                                            Aggregate (cost=173166.50..173166.51 rows=1 width=8) (actual time=23320.353..23320.354 rows=1 loops=1)
                                                            -> Seq Scan on log (cost=0.00..164354.60 rows=3524760 width=8) (actual time=9604.659..20754.970 rows=3568867 loops=1)
                                                            Total runtime: 23320.404 ms
                                                            3. По индексу
                                                            EXPLAIN ANALYZE SELECT COUNT(test_time) FROM test;
                                                            Aggregate (cost=173166.50..173166.51 rows=1 width=4) (actual time=22335.123..22335.124 rows=1 loops=1)
                                                            -> Seq Scan on log (cost=0.00..164354.60 rows=3524760 width=4) (actual time=9584.735..20364.312 rows=3568867 loops=1)
                                                            Total runtime: 22335.177 ms

                                                            никакой разницы :)
                                                              0
                                                              а помойму почти 0.5% =) Тем не менее, count(*) даже писать проще. А count(col) надо использовать только когда действительно нужно не просто кол-во рядов.
                                                                0
                                                                Ну так в постгресе для вычисления count нужно все записи просканировать, т.к. там агрегатные ф-ии по-другому реализованы. Как ни пиши, все равно будет seq scan (если без WHERE).
                                                                0
                                                                а что с автором статьи? инвайт нужен?
                                                                  0
                                                                  да, но топик пока не дотягивает до +50 =(
                                                                    0
                                                                    сбросьте мне его мыло в личку. Мускул вещь полезная
                                                                  0
                                                                  Это MyISAM, какова ситуация с InnoDB — COUNT там весьма ресурсоёмкий
                                                                    0
                                                                    Надо было InnoDB добавить в тест, а также count(1) и count(field) и всё это в одну таблицу, а то споры в этой области будут ещё долго.

                                                                    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                                                    Самое читаемое