Подсчёт общего количества строк выборке в 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.

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



Получается, что всё не так однозначно, как говориться в документации 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. Результаты (не время выборок) для обеих систем одинаковые.