How to make a query using "where MONTH(datetime)" faster? I already tried to add an index on the datetime column, but the result is still unacceptably slow.

SELECT s.id, s.player,
COUNT(case when dg.winner = 1 AND dp.colour &lt;= 5 then 1 when dg.winner = 2 AND dp.colour &gt; 5 then 1 else null end) as totalwin, 
COUNT(case when dg.winner = 2 AND dp.colour &lt;= 5 then 1 when dg.winner = 1 AND dp.colour &gt; 5 then 1 else null end) as totallose, 
COUNT(dg.winner) as totalgames 
       FROM dotaplayers AS dp 
       LEFT JOIN gameplayers AS gp ON gp.gameid = dp.gameid and dp.colour = gp.colour 
       LEFT JOIN stats AS s ON s.player_lower = gp.name 
       LEFT JOIN dotagames AS dg ON dg.gameid = dp.gameid 
       LEFT JOIN games AS g ON g.id = dp.gameid 
       LEFT JOIN bans as b ON b.name=gp.name
       WHERE MONTH(g.datetime) = 4
       GROUP by gp.name
       ORDER BY totalwin DESC LIMIT 0,10

Showing rows 0 - 9 (10 total, Query took 7.7552 seconds.)


If someone wants to help, I can provide a copy of my database, it is only 120 MB in size.
Using the MONTH() function on a column automatically disqualifies any index from usage
You can state the range in the WHERE clause
SELECT s.id, s.player,
COUNT(case when dg.winner = 1 AND dp.colour &lt;= 5 then 1 when dg.winner = 2 AND dp.colour &gt; 5 then 1 else null end) as totalwin, 
COUNT(case when dg.winner = 2 AND dp.colour &lt;= 5 then 1 when dg.winner = 1 AND dp.colour &gt; 5 then 1 else null end) as totallose, 
COUNT(dg.winner) as totalgames 
       FROM dotaplayers AS dp 
       LEFT JOIN gameplayers AS gp ON gp.gameid = dp.gameid and dp.colour = gp.colour 
       LEFT JOIN stats AS s ON s.player_lower = gp.name 
       LEFT JOIN dotagames AS dg ON dg.gameid = dp.gameid 
       LEFT JOIN games AS g ON g.id = dp.gameid 
       LEFT JOIN bans as b ON b.name=gp.name
       WHERE g.datetime &gt;= '2015-04-01 00:00:00'
       AND   g.datetime &lt;  '2015-05-01 00:00:00'
       GROUP by gp.name
       ORDER BY totalwin DESC LIMIT 0,10

or move  the range from the WHERE to the LEFT JOIN
SELECT s.id, s.player,
COUNT(case when dg.winner = 1 AND dp.colour &lt;= 5 then 1 when dg.winner = 2 AND dp.colour &gt; 5 then 1 else null end) as totalwin, 
COUNT(case when dg.winner = 2 AND dp.colour &lt;= 5 then 1 when dg.winner = 1 AND dp.colour &gt; 5 then 1 else null end) as totallose, 
COUNT(dg.winner) as totalgames 
       FROM dotaplayers AS dp 
       LEFT JOIN gameplayers AS gp ON gp.gameid = dp.gameid and dp.colour = gp.colour 
       LEFT JOIN stats AS s ON s.player_lower = gp.name 
       LEFT JOIN dotagames AS dg ON dg.gameid = dp.gameid 
       LEFT JOIN games AS g ON g.id = dp.gameid AND g.datetime&gt;='2015-04-01 00:00:00' AND g.datetime&lt;'2015-05-01 00:00:00'
       LEFT JOIN bans as b ON b.name=gp.name
       GROUP by gp.name
       ORDER BY totalwin DESC LIMIT 0,10

GIVE IT A TRY !!!
UPDATE 2015-07-08 15:19 EST
Try using INNER JOIN on games since you need a specific month
SELECT s.id, s.player,
COUNT(case when dg.winner = 1 AND dp.colour &lt;= 5 then 1 when dg.winner = 2 AND dp.colour &gt; 5 then 1 else null end) as totalwin, 
COUNT(case when dg.winner = 2 AND dp.colour &lt;= 5 then 1 when dg.winner = 1 AND dp.colour &gt; 5 then 1 else null end) as totallose, 
COUNT(dg.winner) as totalgames 
       FROM dotaplayers AS dp 
       LEFT JOIN gameplayers AS gp ON gp.gameid = dp.gameid and dp.colour = gp.colour 
       LEFT JOIN stats AS s ON s.player_lower = gp.name 
       LEFT JOIN dotagames AS dg ON dg.gameid = dp.gameid 
       INNER JOIN games AS g ON g.id = dp.gameid 
       LEFT JOIN bans as b ON b.name=gp.name
       WHERE g.datetime &gt;= '2015-04-01 00:00:00'
       AND   g.datetime &lt;  '2015-05-01 00:00:00'
       GROUP by gp.name
       ORDER BY totalwin DESC LIMIT 0,10