logo-mysql
Время для прочтения: 2 мин. 19 сек.

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_ROWS 7.0 7.0 + 7.0
SELECT + COUNT 1.8 1.8 + 0.05

Вывод

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

Ссылки

Для вставки кода используйте HTML-теги
<pre><code class="php">ваш код</code></pre>

Добавить комментарий

Такой e-mail уже зарегистрирован. Воспользуйтесь формой входа или введите другой.

Вы ввели некорректные логин или пароль

Извините, для комментирования необходимо войти.

4 комментария

по хронологии
по рейтингу сначала новые по хронологии
Елена Милашка

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

А где второй?

Второй с 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().

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