Верх страницы
Обложка к записи SQL_CALC_FOUND_ROWS или не SQL_CALC_FOUND_ROWS?
Время для прочтения: 0 мин. 58 сек.

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;

Результат:

SQL_CALC_FOUND_ROWS_1

Запрос с использованием COUNT:

EXPLAIN SELECT SQL_NO_CACHE COUNT(*)
FROM `count_test`
WHERE `b` = 666;

Результат:

SQL_CALC_FOUND_ROWS_2

По результатам запросов видно, что SQL_CALC_FOUND_ROWS заставляет MySQL обрабатывать все данные в таблице, даже если они не нужны в результате (мы запросили всего пять LIMIT 5), а при использовании COUNT применяется индекс, из-за чего такой запрос выполняется гораздо быстрее.

Проведем те же тесты на таблице с индексом на колонке b:

ТестБез индекса
Full-scan, сек
С индексом на колонке b
Filesort, сек
SQL_CALC_FOUND_ROWS7.07.0 + 7.0
SELECT + COUNT1.81.8 + 0.05

Вывод

Если у нас есть подходящие индексы для WHERE и ORDER, то использование двух отдельных запросов вполне может быть намного быстрее чем один с SQL_CALC_FOUND_ROWS.

Ссылки

Автор: Кобзарёв Михаил

Русский разработчик с 20-ти летним стажем. Работаю с PHP, ООП, JavaScript, Git, WordPress, Битрикс, Joomla, Drupal, Opencart, DLE, Laravel, Moonshine, SuiteCRM.

Оптимизирую сайты под Google Page Speed, настраиваю импорты для больших магазинов на WooCommerce + WP All Import. Пишу плагины на заказ. Все мои услуги.

Веду блог о разработке, дайджест в телеграмме и в ВК.

Вы всегда можете нанять меня.

Комментарии
Подписаться
Уведомить о
guest

4 комментариев
Межтекстовые Отзывы
Посмотреть все комментарии
Елена Милашка
Елена Милашка
9 лет назад

«А что будет, если запрос разбить на два отдельных:»

А где второй?

mihdan
9 лет назад
Ответить на  Елена Милашка

Второй с COUNT(*) ниже

Zak86
7 лет назад


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().

mihdan
7 лет назад
Ответить на  Zak86

Всмысле зачем? Не понял сути вопроса 🙁

Предыдущая запись

Давайте дружить
в Телеграме

Авторский блог вашего покорного слуги в Telegram про web, программирование, алгоритмы, инструменты разработчика, WordPress, Joomla, Opencart, Laravel, Moonshine, фильмы и сериалы