April 29th, 2013, 08:24 AM
This is taking too long
I have a problem.
I am getting the top 10 of the best Clans, however it is taking about 10 seconds. Is there a way to optimize this SQL Query?
SELECT sq.*, (SELECT spieler FROM MuxClanMembers AS mcmi WHERE mcmi.cname = sq.clan AND mcmi.rang = 2) AS owner FROM (SELECT mcm.cname AS clan, SUM(ms.kills) AS kills, SUM(ms.deaths) AS deaths, IF(deaths=0,kills,kills / deaths) AS kdr FROM MuxClanMembers AS mcm LEFT JOIN MuxStats AS ms ON mcm.spieler = ms.spieler WHERE mcm.eingeladen=0 GROUP BY mcm.cname ORDER BY kdr DESC LIMIT 10) AS sq;
Basically this is what I get:
ClanName | ClanOwner | Kills of the Clan | Deaths of the Clan | KDR (Kill/Death Ratio) of the Clan
Thank you in advance.
April 29th, 2013, 02:37 PM
What is the result if you run an EXPLAIN SELECT on the query?
Here is the result.
Sorry for the late reply.
How does your table looks like? what indexes do you have?
Especially important with the table mcm (MuxClanMembers) as it is using filesort.