MySQL query optimization

MySQLoptimization

I'm quite new to big MySQL queries (I used to extract raw data from tables then manipulate them with PHP) so I'd like to know if my query below is too "heavy" or slow for some reason (it seems to be too big to me) and how I can optimize it. Here is my query:

SELECT
    L.id,
    L.role,
    L.first_name,
    L.last_name,
    L.birth_date,
    L.team_name,
    L.team AS team_id,
    L.photo,
    L.email,
    L.played,
    L.vote_average,
    L.goal_sum AS goal,
    L.score + IFNULL(L.c_mvp, 0)*10 AS score,
    L.y_card_sum AS y_card,
    L.r_card_sum AS r_card,
    IFNULL(L.c_mvp, 0) AS mvp,
    IFNULL(unbeaten, 0) AS unbeaten
FROM (
    SELECT * FROM (
        SELECT
            dc_player. *,
            dc_team.name AS team_name,
            COUNT( dc_match_entry.player_id ) AS played,
            AVG( dc_match_entry.vote ) AS vote_average,
            SUM( dc_match_entry.goal ) AS goal_sum,
            SUM( dc_match_entry.vote ) *10 + SUM( dc_match_entry.goal ) *10 - SUM( dc_match_entry.r_card ) *10 - SUM( dc_match_entry.y_card ) *5 - SUM( dc_match_entry.own_goal ) *10 AS score,
            SUM( dc_match_entry.y_card ) AS y_card_sum,
            SUM( dc_match_entry.r_card ) AS r_card_sum
        FROM
            dc_player,
            dc_match_entry,
            dc_team
        WHERE 
            dc_player.id = dc_match_entry.player_id AND
            dc_player.team = dc_team.id
        GROUP BY
            dc_player.id
    ) LOL
    LEFT JOIN (
        SELECT
            COUNT(dc_match.mvp) c_mvp,
            mvp AS player_id
        FROM dc_match
        WHERE
            dc_match.mvp IS NOT NULL
        GROUP BY dc_match.mvp
    ) ROFL
    ON ROFL.player_id = LOL.id
    ORDER BY 
        LOL.score DESC
) L
LEFT OUTER JOIN(
    SELECT
        dc_player.id,
        COUNT(dc_match.id) AS unbeaten
    FROM
        dc_match,
        dc_match_entry,
        dc_player
    WHERE
        (
            (dc_player.team=dc_match.host AND
            dc_match.guest_score=0
            ) OR
            (
                dc_player.team=dc_match.guest AND
                dc_match.host_score=0
            )
        ) AND
        dc_player.id = dc_match_entry.player_id AND dc_match.id = dc_match_entry.match_day
    GROUP BY
        dc_player.id
) I
ON
    L.id = I.id
GROUP BY
    id

I can provide table structures too if needed.

Best Answer

It may be miserably slow (until 5.6.7 or MariaDB 5.5) because of the following. Let me give a simplified version:

SELECT ... FROM ( SELECT ... ) a JOIN ( SELECT ... ) b ON a.x = b.x

There are (and cannot be) indexes on either of the temp tables (a, b). If there are, say, 10K rows in each of a and b, then the JOIN will have to do 100M compares (10K*10K). Slow.

If one of the subqueries boils down (via GROUP BY, LIMIT, or...) to only a few rows, then performance won't be too bad.

As a general rule, avoid subqueries. Some subqueries are ok -- namely those that shrink the number of rows (GROUP BY, etc). But the JOIN problem above still stands.

The general 'fix' for a subquery is to turn it into a JOIN. But that does not apply here because most have GROUP BY. So... Consider CREATEing TEMPORARY TABLEs with indexes for some or all of the subqueries.

Another performance killer is OR. Generally it makes it impossible to use an index on the fields in the OR. Sometimes the fix is to do two SELECTs and UNION ALL/DISTINCT them.

Wade through some of those suggestions, then come back for more advice. Bring SHOW CREATE TABLE next time.

Related Question