MySQL query optimization

Question

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.

• Alain Collins: Your table structure and the goal of the query would be helpful.
• András Váczi: "Premature optimization is the root of all evil" - before diving into optimization, you'd be better knowing that your query is slow. If it isn't, why optimizing it? Long queries aren't necessarily bad ones - I have a few with several hundred lines each in production.

Answer 1

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.