I’m a novice regarding mysql optimization and I require some help with regards to reading the output from EXPLAIN with a particular query that uses subqueries.
My Question:
The inner queries use union and the rows from these queries when added together will be 530 (I understand that with union the rows must not be multiplied but added) but what will the final number of rows examined be? It does not make sense to me to say 530 x 38 x 19 x 19 as the outer queries simply retrieve the data from the inner queries, would it then be correct to say that to calculate the rows examined it would be 530 + 38 + 19 + 19 which equals 606 rows?
What the Query relates to:
The following query relates to a Javelin Throwing Competition whereby two people are individually given an opportunity to throw a Javelin as far possible. Each competitor receives one throw from the one side (Side A) of the field. The competitor who throws the furtherest will receive 1 point. Should both competitors achieve the same distance, then they will each be awarded with half a point. At a later date the same competitors will receive another throw each from the opposite side (Side B) of the same field where the same point structure applies.
The actual query: (see attached file titled “query.png” for a neater representation of this query)
select z.competitor_id, z.rank, z.events, z.distance, z.oppositionDistance, z.pts
from (select y.competitor_id, y.competitor_name, y.events, y.distance, y.oppositionDistance, y.pts, @num := @num + 1 as rank
from (
select x.competitor_id, x.competitor_name, sum(x.events) as events, sum(x.distance) as distance, sum(x.oppositionDistance) as oppositionDistance, sum(x.pts) as pts
from (
(select count(e.event_id) as events,
sum(e.side_a) as distance,
sum(side_b) as oppositionDistance,
sum(CASE WHEN e.side_a > e.side_b THEN 1
ELSE
CASE WHEN e.side_a < e.side_b THEN 0
ELSE 0.5
END END) as pts, c.competitor_id, c.name as competitor_name
from competitors c, events e
where e.competition_id = 3 and c.competitor_id = e.side_a_competitor_id and e.season_id = 10
group by c.competitor_id)
union all
(select count(e.event_id) as events,
sum(e.side_b) as distance,
sum(side_a) as oppositionDistance,
sum(CASE WHEN e.side_b > e.side_a THEN 3
ELSE
CASE WHEN e.side_b < e.side_a THEN 0
ELSE 1
END END) as pts, c.competitor_id, c.name as competitor_name
from competitors c, events e
where e.competition_id = 3 and c.competitor_id = e.side_b_competitor_id and e.season_id = 10
group by c.competitor_id)) as x
group by x.competitor_id
order by x.pts desc, x.events, x.distance desc, x.oppositionDistance, x.competitor_name) as y) as z
where z.competitor_id = 42
What the Query Does:
This query is used on a database that stores competitors and the events that they’ve competed in. The inner queries (ie. “x” in the above query) basically determine the number of points and the distances achieved when the competitor competed on field A and then on field B and then sums the results. The outer queries basically continue to just filter that data and lastly only one of the competitors data (competitor with id of 42 in this case) is extracted. The data for all the competitors had to first be worked out so that the overall rank can be obtained for the individual competitors.
The EXPLAIN Output:
When I use EXPLAIN with this query I get the output that can be seen in the attached screenshot/file titled “explain.png”.
Any advice would be appreciated.