How to optimize this MySQL query

Question

This query was working fine when the database was small, but now that there are millions of rows in the database, I am realizing 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

Answer 1

It appears you need an index on username, battle_type, outcome or username, outcome, battle_type.

Answer 2

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?

Answer 3

Lets see, what you are doing:

1. Find rows that are of battle_type = 0 and outcome < 2
2. Order by username for grouping
3. Compute aggregations and fold rows to distinct usernames
4. 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.