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

Question

[Вадим]

То, что нередко использование 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 вполне ожидаемо получаем все сто тыщ строк:

Добавляем в запрос поле, которое, казалось бы, ни на что не должно влиять:

EXPLAIN SELECT SQL_CALC_FOUND_ROWS id, user_id
FROM aab ORDER BY created_at DESC LIMIT 0, 20

И видим, что индекс уже не используется, и идет перебор по всем записям "в сухую":

В результате выборка выполняется почти на два порядка дольше. Как так? Почему? Можно ли это как-то побороть? Или тупо забить и все запросы переписывать под COUNT(*)?

Answer 1

[Евгений Самсонов]

Кроме 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;

Comments

[Вадим]

Да, спасибо, оба варианта работают.

Вариант 1 удивил: ведь поле `user_id` не используется ни в выборке, ни в сортировке, а индекс ему дай. Но самый большой недостаток этого варианта, как я понимаю: если список получаемых полей будет меняться в выборке, то их все придется в индекс включать. Как-то не очень. А вариант 2, хоть и медленней, но более универсальный

[Евгений Самсонов]

Вадим, user_id есть в SELECT. Тут анализатор видит, что за user_id все равно придется сходить на диск — его нет в индексе и, так как под выборку попадают все записи таблицы, решает, что лучше просто все перебрать без индекса (видимо не учитывая, что это еще отсортировать придется). То есть наличие/отсутствие в индексе выбираемых селектом полей влияет на то, как будет построен запрос

[Вадим]

Лентюй, спасибо, выходит, голову надо ломать не как такие запросы оптимизировать, а как вообще от них уйти