How to optimize this MySQL query
This query was working fine when the database was small, but now that there are millions of rows in the database, I am re开发者_如何学Pythonalizing I should have looked at optimizing this earlier. It is looking at over 600,000 rows and is Using where; Using temporary; Using filesort (which leads to an execution time of 5-10 seconds). It is using an index on the field '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
It appears you need an index on username, battle_type, outcome
or username, outcome, battle_type
.
First thing would be to make sure you have good indexes (as others have mentioned).
However, it looks like you're creating a leaderboard of some sort for a web page. My first question would be - do you really need to execute this query in real time? Could you create a table in your database (or add a wins and losses column to the users table) with the results of this query and simply refresh it periodically?
Lets see, what you are doing:
- Find rows that are of battle_type = 0 and outcome < 2
- Order by username for grouping
- Compute aggregations and fold rows to distinct usernames
- Order by dinamically calculated fields
On steps 3 and 4 you have no influence. Step 2 in its current form can not benefit from any indices, since outcome < 2
is range condition, index on (battle_type, outcome, username) looks very tempting though.
Assuming that outcome
is enumeration of 0,1,2,3...
you can change the range condition to equality comparison and benefit from index on (battle_type, outcome, 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
If outcome
is not enumeration, index on (battle_type, outcome) will do. Index on (battle_type) only is excess now, since battle_type
is a prefix in the compound index.
精彩评论