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.