SitePoint Sponsor

User Tag List

Results 1 to 1 of 1
  1. #1
    SitePoint Member
    Join Date
    Aug 2013
    0 Post(s)
    0 Thread(s)

    Question Calculate Rows Examined for Query with SubQueries

    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.distance, z.oppositionDistance, z.pts
    from (select y.competitor_id, y.competitor_name,, y.distance, y.oppositionDistance, y.pts, @num := @num + 1 as rank
    from (
    select x.competitor_id, x.competitor_name, sum( 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
    CASE WHEN e.side_a < e.side_b THEN 0
    ELSE 0.5
    END END) as pts, c.competitor_id, 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
    CASE WHEN e.side_b < e.side_a THEN 0
    ELSE 1
    END END) as pts, c.competitor_id, 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.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.
    Attached Images Attached Images

Tags for this Thread


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts