Использование индекса, используя временный, с помощью filesort - как это исправить?
Я работаю над системой отслеживания событий, которая использует несколько таблиц поиска, а также таблицу первичного ведения журналов. В отчете, который я пишу, объект может быть выбран для просмотра статистики. Интерфейс показывает все объекты в порядке убывания важности (т.е. Попадания).
Схема для двух таблиц (слегка обрезанная, но вы получаете суть):
CREATE TABLE IF NOT EXISTS `event_log` (
`event_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(5) DEFAULT NULL,
`object_id` int(5) DEFAULT NULL,
`event_date` datetime DEFAULT NULL,
PRIMARY KEY (`event_id`),
KEY `user_id` (`user_id`),
KEY `object_id` (`object_id`)
);
CREATE TABLE IF NOT EXISTS `lookup_event_objects` (
`object_id` int(11) NOT NULL AUTO_INCREMENT,
`object_desc` varchar(255) NOT NULL,
PRIMARY KEY (`object_id`)
);
Ниже приведен запрос, с которым я столкнулся. Он отлично работает с моей таблицей ~ 100 записей, но EXPLAIN меня немного беспокоит.
explain SELECT
el.object_id,
leo.object_desc,
COUNT(el.object_id) as count_rows
FROM
event_log el
LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
GROUP BY
el.object_id
ORDER BY
count_rows DESC,
leo.object_desc ASC
Возвращает:
Using index; Using temporary; Using filesort
Итак - что случилось с моей схемой и/или запросом для MySQL, чтобы вернуться на temporary
и filesort
? Или он оптимизирован, так как он может использовать ORDER BY?
Ответы
Ответ 1
Ну, doc дает точные причины появления "Использовать временный":
Временные таблицы могут создаваться в таких условиях, как:
Если есть предложение ORDER BY и другое предложение GROUP BY, или если ORDER BY или GROUP BY содержит столбцы из таблиц, отличных от первая таблица в очереди объединений, создается временная таблица.
DISTINCT в сочетании с ORDER BY может потребовать временную таблицу.
Если вы используете параметр SQL_SMALL_RESULT, MySQL использует встроенную память временная таблица, если запрос также не содержит элементов (описанных позже), которые требуют хранения на диске.
Быстрое сканирование показывает, что вы страдаете от # 1.
И этот блог с 2009 года говорит, что "использование filesort" означает, что сортировка не может быть выполнена с индексом. Поскольку вы заказываете по вычисленному полю, это тоже будет верно.
Итак, что "неправильно".
Ответ 2
Это следующие условия, при которых создаются временные таблицы.
Запросы UNION используют временные таблицы.
В некоторых представлениях требуются временные таблицы, которые оцениваются с использованием алгоритма TEMPTABLE или которые используют UNION или агрегацию.
Если есть предложение ORDER BY и другое предложение GROUP BY, или если ORDER BY или GROUP BY содержат столбцы из таблиц, отличных от первой таблицы в очереди соединений, создается временная таблица.
DISTINCT в сочетании с ORDER BY может потребовать временную таблицу.
Если вы используете параметр SQL_SMALL_RESULT, MySQL использует временную таблицу в памяти, если в запросе также нет элементов (описанных ниже), которые требуют хранения на диске.
Следуйте по этой ссылке mysql:
http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html