Любой web-программист сталкивался с постраничным выводом информации. Например, в таблице лежит 1000 новостей и нужно выводить их по 10 штук на странице, а внизу вывести листалку страниц.
Программист пишет 2 запроса:
1) SELECT * FROM news WHERE activity=1 LIMIT 0, 10 // выборка записей 2) SELECT COUNT(*) FROM news WHERE activity=1// узнаем кол-во всех новостей
Второй запрос нужен для вывода листалки. Однако в MySQL есть достаточно удобная возможность объединения этих двух запросов в один.
В запросе SELECT перед списком столбцов необходимо указать опцию SQL_CALC_FOUND_ROWS. Вот начало описания синтаксиса конструкции SELECT.
SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr, …
Таким образом, выполняя запрос SELECT SQL_CALC_FOUND_ROWS MySQL подсчитает полное число строк, подходящих под условие запроса без LIMIT, и сохранить это число в памяти. Естественно, имеет смысл запрос SELECT SQL_CALC_FOUND_ROWS только при использовании ограничения (LIMIT). Сразу после выполнения запроса на выборку для получения количества записей нужно выполнить еще один SELECT-запрос: SELECT FOUND_ROWS ();. В результате MySQL вернет одну строку с одним полем, в котором и будет храниться число строк.
Пример запросов:
1) SELECT SQL_CALC_FOUND_ROWS * FROM news WHERE activity=1 LIMIT 10 2) SELECT FOUND_ROWS();
Первый запрос вернет (выведет) 10 строк таблицы news, для которых выполняется условие activity=1. Второй вызов команды SELECT возвратит количество строк, которые возвратила бы первая команда SELECT, если бы она была написана без выражения LIMIT. Хотя при использовании команды SELECT SQL_CALC_FOUND_ROWS, MySQL должен пересчитать все строки в наборе результатов, этот способ все равно быстрее, чем без LIMIT, так как не требуется посылать результат клиенту.
Пример запросов из PHP:
$result = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM news LIMIT 10");
while ($row = mysql_fetch_assoc($result)) {
var_dump($row);
}
$num_rows = mysql_num_rows($result);
echo "$num_rows Rows";
В результате выполнения кода PHP выведет 10 строк из таблицы news , а затем целочисленное значение количества строк, соответствующих запросу (без учета LIMIT).
> Хотя при использовании команды SELECT SQL_CALC_FOUND_ROWS, MySQL должен пересчитать все строки в наборе результатов, этот способ все равно быстрее, чем без LIMIT, так как не требуется посылать результат клиенту.
Вы серьёзно считаете, что это состоятельный аргумент?
Конечно, переслать N байт быстрее, чем переслать N+4(8/10/неважно) байт. Но расход вычислительных мощностей-то не в этом, правда?
Проблема в том, что второй запрос будет либо снова пересчитывать строки (вот здесь затраты), либо нужно закешировать результаты селекта (количество - тоже часть результата).
Копать надо в сторону оптимизации индексов таблицы в сиквеле и кеширования на стороне PHP, а не подсчета строк. А такая оптимизация никаких приростов в производительности не даст на базах с малым количеством информации, а на больших только увеличит затраты на подсчет результатов. Лучше использовать старый проверенный SELECT COUNT(*) с правильно проставленными индексами, и кешировать до изменения данных в таблице, чем сначала выбирать а потом считать.
На самом деле я использую SQL_CALC_FOUND_ROWS только лишь из-за удобства конструкции (не нужно дублировать запрос с COUNT). На небольших проектах совершенно не имеет значения какую конструкцию использовать - при правильных индексах обе будут работать быстро. На больших проектах гораздо важнее правильное кэширование.
Лично я предпочитаю писать красивый код, даже если это в ущерб производитльности - мощности сервера сейчас стоят копейки. Прошли те времена когда програмисты боролись за каждый байт памяти.
Бывает нужно выбирать записи не только в прямом поряде но и обратном. Здесь представлены эксерименты по измерению времени выборок (сек). Время выборки в прямом и обратном порядке в начале, в середине и конце таблицы.
Выбираются 100 строк из таблицы в 15198 строк . Таблица вида:
1;aaaaaaaaaaaaaaaaaaaaaaaaaaaaa;999;999,9;aaaaaaaaaa;aaaaaaaaaaaaa;aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;32
Первое и последнее поля - индексы.
# Общий вывод
- Как при прямой выборке, так и при обратной, подсчет строк вести с помощью COUNT, а не SQL_CALC_FOUND_ROWS.
- В обратном порядке никогда не извлекать. Правда придется подсчитать строки и обратить массив, но это работает всегда быстрее, чем даже обратная выборка без подсчета.
#1
# Реверс с помощью array_reverse() не влияет на время, то есть лучше извлекать в прямом порядке и обращать массив в PHP
В начале
SELECT * FROM `alifpress_tab_drugs--` WHERE blockId=86 LIMIT 0, 100
0,002529 | rev: 0,002408
В середине
SELECT * FROM `alifpress_tab_drugs--` WHERE blockId=86 LIMIT 7000, 100
0,033052 | rev: 0,033089
В конце
SELECT * FROM `alifpress_tab_drugs--` WHERE blockId=86 LIMIT 15097, 100
0,068655 | rev: 0,068438
#2
# Последние 100 строк в обратном порядке (ORDER BY и DESC) извлекаются на 21% дольше, чем подсчет строк (COUNT), извлечение последних 100 строк в прямом порядке и обращение массива! И это было лучшее проявление обратной выборки, то есть выборка в конце таблицы (ORDER BY recId DESC LIMIT 0, 100). Применять же обратную выборку в середине или начале таблицы вообще не стоит - это время больше в 12 раз, чем при выборке в конце таблицы!
SELECT COUNT(*) AS count FROM `alifpress_tab_drugs--`
SELECT * FROM `alifpress_tab_drugs--` WHERE blockId=86 LIMIT 15097, 100
array_reverse()
0,096872
0,091073
0,088885
SELECT * FROM `alifpress_tab_drugs--` WHERE blockId=86 ORDER BY recId DESC LIMIT 0, 100
0,112414
0,111679
0,109608
#3.
# Подсчет с помощью COUNT и выборка 100 строк в прямом порядке работает 3,5 раз быстрее, чем с помощью SQL_CALC_FOUND_ROWS.
SELECT COUNT(*) AS count FROM `alifpress_tab_drugs--`
SELECT * FROM `alifpress_tab_drugs--` WHERE blockId=86 LIMIT 0, 100
0,020637
0,020737
0,020941
SELECT SQL_CALC_FOUND_ROWS * FROM `alifpress_tab_drugs--` WHERE blockId=86 LIMIT 0, 100
0,073836
0,072075
0,071991
#4.
# Для подсчета строк при обратной выборке SQL_CALC_FOUND_ROWS ни в коем случае не использовать. Этот запрос как бы делает свою работу за одно с выборкой, но лучше подсчитать число строк отдельно с помощью COUNT.
При обратной выборке на всех диапазонах
SELECT SQL_CALC_FOUND_ROWS * FROM `alifpress_tab_drugs--` WHERE blockId=86 ORDER BY recId DESC LIMIT 0, 100
1,23811
1,265026
1,199161