CODEWITHSUNDEEP
mysqlsqllaravel
ronscript
ronscript

Reputation: 407

How to optimize query when using sub-queries in left join

Tables: Please take a look here to see tables. How to query counting specific wins of team and find the winner of the series

Questions:

Summary

As you can see in the example query this part is use many times.

WHERE leagueid = 2096
AND start_time >= 1415938900
AND ((matches.radiant_team_id= 1848158 AND matches.dire_team_id= 15)
OR (matches.radiant_team_id= 15 AND matches.dire_team_id= 1848158))

SELECT matches.radiant_team_id,
       matches.dire_team_id,
       matches.radiant_name,
       matches.dire_name,
       TA.Count AS teamA,
       TB.Count AS teamB,
       TA.Count + TB.Count AS total_matches,
       SUM(TA.wins),
       SUM(TB.wins),
       (CASE
            WHEN series_type = 0 THEN 1
            WHEN series_type = 1 THEN 2
            WHEN series_type = 2 THEN 3
        END) AS wins_goal
FROM matches
LEFT JOIN
  (SELECT radiant_team_id,
          COUNT(id) AS COUNT,
          CASE
              WHEN matches.radiant_team_id = radiant_team_id && radiant_win = 1 THEN 1
          END AS wins
   FROM matches
   WHERE leagueid = 2096
     AND start_time >= 1415938900
     AND ((matches.radiant_team_id= 1848158
           AND matches.dire_team_id= 15)
          OR (matches.radiant_team_id= 15
              AND matches.dire_team_id= 1848158))
   GROUP BY radiant_team_id) AS TA ON TA.radiant_team_id = matches.radiant_team_id
LEFT JOIN
  (SELECT dire_team_id,
          COUNT(id) AS COUNT,
          CASE
              WHEN matches.dire_team_id = dire_team_id && radiant_win = 0 THEN 1
          END AS wins
   FROM matches
   WHERE leagueid = 2096
     AND start_time >= 1415938900
     AND ((matches.radiant_team_id= 1848158
           AND matches.dire_team_id= 15)
          OR (matches.radiant_team_id= 15
              AND matches.dire_team_id= 1848158))
   GROUP BY dire_team_id) AS TB ON TB.dire_team_id = matches.dire_team_id
WHERE leagueid = 2096
  AND start_time >= 1415938900
  AND ((matches.radiant_team_id= 1848158
        AND matches.dire_team_id= 15)
       OR (matches.radiant_team_id= 15
           AND matches.dire_team_id= 1848158))
GROUP BY series_id

Scheduled Matches

ID| leagueid| team_a_id| team_b_id| starttime
 1|     2096|   1848158|        15| 1415938900

Upvotes: 2

Views: 93

Answers (2)

ffflabs
ffflabs

Reputation: 17481

I believe it can be done without subqueries.

I made the following match table

enter image description here

And used the following query to group results, one line per series

SELECT 
      matches.leagueid,
      matches.series_id,
      matches.series_type,
      COUNT(id) as matches,
      IF(radiant_team_id=1848158,radiant_name, dire_name) AS teamA,
      IF(radiant_team_id=1848158,dire_name, radiant_name) AS teamB,
      SUM(CASE
           WHEN radiant_team_id=1848158 AND radiant_win=1  THEN 1
           WHEN dire_team_id=1848158 AND radiant_win=0 THEN 1
           ELSE 0 END) AS teamAwin,
      SUM(CASE
           WHEN radiant_team_id=15 AND radiant_win=1  THEN 1
           WHEN dire_team_id=15 AND radiant_win=0 THEN 1
           ELSE 0 END) AS teamBwin

FROM `matches` 
WHERE leagueid = 2096
     AND start_time >= 1415938900
AND dire_team_id IN (15, 1848158)
AND radiant_team_id IN  (15, 1848158)
group by leagueid,series_id,series_type,teamA,teamB

which yields the following result

enter image description here

Please note that, when grouping the results of one series, there isn't such thing as radiant team or dire team. The radiant and dire roles might be switched several times during the same series, so I only addressed the teams as teamA and teamB.

Now, looking at your prior question, I see that you need to determine the series winner based on the series type and each team victories. This would need to wrap the former query and use it as a subquery such as

SELECT matchresults.*,
      CASE series_type
      WHEN 0 then IF(teamAwin>=1, teamA,teamB)
      WHEN 1 then IF(teamAwin>=2, teamA,teamB)
      ELSE IF(teamAwin>=3, teamA,teamB)
      END as winner

from ( THE_MAIN_QUERY) as matchresults

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

There may be more efficient ways to get the results you want. But, to make this query more efficient, you can add indexes. This is the repeated where clause:

WHERE leagueid = 2096 AND
      start_time >= 1415938900 AND
      ((matches.radiant_team_id= 1848158 AND matches.dire_team_id= 15) OR
       (matches.radiant_team_id= 15 AND matches.dire_team_id= 1848158))

Conditions with or are hard for the optimizer. The following index will be helpful: matches(leagueid, start_time). A covering index (for the where conditions at least) is matches(leagueid, start_time, radiant_team_id, dire_team_id). I would start with this latter index and see if that improves performance sufficiently for your purposes.

Upvotes: 1

Related Questions