#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0

    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.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,614
    Rep Power
    1945
    What is the result if you run an EXPLAIN SELECT on the query?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0
    abload.de/img/screenshot2013-05-07a22uzh.png
    Here is the result.
    Sorry for the late reply.
  6. #4
  7. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,614
    Rep Power
    1945
    How does your table looks like? what indexes do you have?
    Especially important with the table mcm (MuxClanMembers) as it is using filesort.

IMN logo majestic logo threadwatch logo seochat tools logo