5 апр. 2011 г.

SQL_CALC_FOUND_ROWS и с постраничный вывод информации

Любой 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).