vshemarov
@vshemarov

Почему с SQL_CALC_FOUND_ROWS не работает индекс?

То, что нередко использование SQL_CALC_FOUND_ROWS более затратно, чем банальное COUNT(*) - это я знаю. Но я не ожидал, что это может быть настолько чудовищно затратно. Итак, имеем простенькую таблицу:
CREATE TABLE `aab` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL
  `created_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB;

ALTER TABLE `aab`
  ADD PRIMARY KEY (`id`),
  ADD KEY `created_at` (`created_at`);

ALTER TABLE `aab`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

В таблице около 100К записей. Нам нужно получать постранично записи с сортировкой по полю `created_at`. Делаем запрос:
EXPLAIN SELECT SQL_CALC_FOUND_ROWS id
FROM aab ORDER BY created_at DESC LIMIT 0, 20
Видим, что используется индекс created_at, в rows вполне ожидаемо получаем все сто тыщ строк:
5f5c8404ae1ed570616100.png
Добавляем в запрос поле, которое, казалось бы, ни на что не должно влиять:
EXPLAIN SELECT SQL_CALC_FOUND_ROWS id, user_id
FROM aab ORDER BY created_at DESC LIMIT 0, 20
И видим, что индекс уже не используется, и идет перебор по всем записям "в сухую":
5f5c84a487870347897126.png
В результате выборка выполняется почти на два порядка дольше. Как так? Почему? Можно ли это как-то побороть? Или тупо забить и все запросы переписывать под COUNT(*)?
  • Вопрос задан
  • 54 просмотра
Решения вопроса 1
bitniks
@bitniks
Go/PHP/Symfony developer
Кроме SQL_CALC_FOUND_ROWS добавлен user_id. Это поле отсутствует в индексе и бд нужно сходить на диск, в отличии от первого запроса, где id берется из покрывающего индекса. Поэтому во втором запросе анализатор решает, что лучше не использовать индекс.

Самый оптимальный вариант сделать составной покрывающий индекс
ALTER TABLE `aab` ADD KEY `created_at_user_id` (`created_at`, `user_id`);

Второй вариант использовать FORCE INDEX. Это медленнее покрывающего индекса, так как все равно бд придется сходить на диск, но зато уже не будет сортировки без индекса
EXPLAIN SELECT SQL_CALC_FOUND_ROWS id, user_id
FROM aab FORCE INDEX (created_at)
ORDER BY created_at DESC LIMIT 0, 20;
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы