SQL_CALC_FOUND_ROWS или не SQL_CALC_FOUND_ROWS?
Занимаясь оптимизацией клиентских запросов, я довольно часто вижу запросы, в которых используется SQL_CALC_FOUND_ROWS. Многие думают, что данная конструкция намного быстрее, чем выполнение двух запросов: получение данных SELECT и подсчет количества записей при помощи COUNT. Попробуем разобраться что к чему.
Создадим следующую таблицу:
CREATE TABLE `count_test` (
`a` int(10) NOT NULL AUTO_INCREMENT,
`b` int(10) NOT NULL,
`c` int(10) NOT NULL,
`d` varchar(32) NOT NULL,
PRIMARY KEY (`a`),
KEY `bc` (`b`,`c`)
) ENGINE=MyISAM
Заполняем её случайными данными:
mysql_connect( '127.0.0.1', 'root' );
mysql_select_db( 'test' );
$num = 10000000;
for ( $i = 0; $i < $num; $i++ ) {
$b = $i % 1000;
$sql = 'INSERT INTO `count_test`
SET `b` = {$b},
`c` = ROUND(RAND() * 10),
`d` = MD5({$i})
';
mysql_query( $sql );
}
Сначала попытаемся выполнить запрос, используя проиндексированную колонку b в выражении WHERE:
SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS *
FROM `count_test`
WHERE `b` = 555
ORDER BY `c` LIMIT 5;
В итоге, для каждого значения b запрос выполнялся 20-100 секунд в первый раз и 2-5 секунд — после кэширования. Такая разность объясняется издержками ввода-вывода, которые требуются MySQL для обработки 10000 строк без конструкции LIMIT.
А что будет, если запрос разбить на два отдельных:
SELECT SQL_NO_CACHE *
FROM `count_test`
WHERE `b` = 666
ORDER BY c LIMIT 5;
Первый раз запрос выполняется 0.01-0.11 сек, 0.00-0.02 сек — в последующие. Теперь посмотрим, сколько будет работать COUNT:
SELECT SQL_NO_CACHE COUNT(*)
FROM `count_test`
WHERE `b` = 666;
Результат ошеломляющий — 0.00-0.04 сек. Получается, что общее время выполнения запросов SELECT и COUNT лежит в промежутке от 0.00 сек до 0.15 сек, что намного меньше времени выполнения исходного запроса. Что на это скажет EXPLAIN:
EXPLAIN SELECT SQL_CALC_FOUND_ROWS *
FROM `count_test`
WHERE `b` = 999
ORDER BY `c` LIMIT 5;
Результат:
Запрос с использованием COUNT:
EXPLAIN SELECT SQL_NO_CACHE COUNT(*)
FROM `count_test`
WHERE `b` = 666;
Результат:
По результатам запросов видно, что SQL_CALC_FOUND_ROWS заставляет MySQL обрабатывать все данные в таблице, даже если они не нужны в результате (мы запросили всего пять LIMIT 5), а при использовании COUNT применяется индекс, из-за чего такой запрос выполняется гораздо быстрее.
Проведем те же тесты на таблице с индексом на колонке b:
| Тест | Без индекса Full-scan, сек | С индексом на колонке b Filesort, сек |
|---|---|---|
| SQL_CALC_FOUND_ROWS | 7.0 | 7.0 + 7.0 |
| SELECT + COUNT | 1.8 | 1.8 + 0.05 |
Вывод
Если у нас есть подходящие индексы для WHERE и ORDER, то использование двух отдельных запросов вполне может быть намного быстрее чем один с SQL_CALC_FOUND_ROWS.
«А что будет, если запрос разбить на два отдельных:»
А где второй?
Второй с COUNT(*) ниже
public function getPages($db, $start, $per_page){
$this->db_name = $db;
// $query = $this->connect->query("SELECT * FROM ".$this->db_name." ORDER BY `id` DESC LIMIT $start, $per_page");
$query = $this->connect->query("SELECT SQL_CALC_FOUND_ROWS * FROM ".$this->db_name." ORDER BY `id` DESC LIMIT $start, $per_page");
$all_db = $this->tableData = mysqli_fetch_all($query);
return $all_db;
}
А навіща FOUND_ROWS().
Всмысле зачем? Не понял сути вопроса 🙁