Как оптимизировать этот запрос MySQL
Этот запрос хорошо работал, когда база данных была маленькой, но теперь, когда существуют миллионы строк в базе данных, я понимаю, что должен был посмотреть на оптимизацию этого ранее. Это смотрит на более чем 600 000 строк и Использует где; Используя временный файл; Используя filesort (который приводит ко времени выполнения 5-10 секунд). Это использует индекс на поле 'battle_type'.
SELECT username, SUM( outcome ) AS wins, COUNT( * ) - SUM( outcome ) AS losses
FROM tblBattleHistory
WHERE battle_type = '0' && outcome < '2'
GROUP BY username
ORDER BY wins DESC , losses ASC , username ASC
LIMIT 0 , 50
3 ответа
Первым делом нужно убедиться, что у вас хорошие индексы (как уже упоминали другие).
Однако похоже, что вы создаете своего рода таблицу лидеров для веб-страницы. Мой первый вопрос: действительно ли вам нужно выполнять этот запрос в реальном времени? Не могли бы вы создать таблицу в своей базе данных (или добавить столбец побед и поражений в таблицу пользователей) с результатами этого запроса и просто периодически обновлять ее?
Похоже, вам нужен индекс для имени пользователя, типа битвы, результата или имени пользователя, результата, типа битвы .
Посмотрим, что вы делаете:
- Найдите строки, которые имеют battle_type = 0 и результат <2
- Упорядочить по имени пользователя для группировки
- Вычислить агрегаты и свернуть строки в отдельные имена пользователей
- Упорядочить по динамически вычисляемым полям
На шагах 3 и 4 вы не имеете никакого влияния. Шаг 2 в его нынешней форме не может получить выгоду от каких-либо индексов, поскольку результат <2 является условием диапазона, индекс по (battle_type, результат, имя пользователя) выглядит очень заманчиво.
Предполагая, что результат является перечислением 0,1,2,3 ... , вы можете изменить условие диапазона на сравнение равенства и получить выгоду от индекса на (battle_type, result, username):
SELECT username, SUM( outcome ) AS wins, COUNT( * ) - SUM( outcome ) AS losses
FROM tblBattleHistory
WHERE battle_type = 0 AND outcome IN (0, 1)
GROUP BY username
ORDER BY wins DESC , losses ASC , username ASC
LIMIT 0 , 50
Если результат не является перечислением, то будет индекс по (battle_type, result). Индекс только для (battle_type) теперь является избыточным, поскольку battle_type является префиксом в составном индексе.