How to optimize this MySQL query

Question

This query worked fine when the database was small, but now that there are millions of rows in the database, I understand that I should have thought about optimizing this earlier. It looks over 600,000 lines and uses where; Use of temporary; Using filesort (resulting in a runtime of 5-10 seconds). It uses the index in the "battle_type" field.

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

First of all, you need to make sure that you have good indexes (as others mentioned).

, - -. : ? ( ) ?

Answer 2

, username, battle_type, outcome username, outcome, battle_type.

Answer 3

, :

• , battle_type = 0 < 2

3 4 . 2 - , outcome < 2 , on (battle_type, result, username) .

Assuming this outcomeis an enumeration 0,1,2,3..., you can change the range condition to equality comparison and benefit from the index (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

If outcomeit is not an enumeration, then the on (battle_type, result) index will act. Index on (battle_type) is now only an excess, since it battle_typeis a prefix in a composite index.