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

Question

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:

How to make query more optimize?
How to reduce query redundancy?
How to make this query more faster?

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

score 1 · Answer 1 · answered Nov 23 '14 at 14:14

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.

score 1 · Accepted Answer · answered Nov 23 '14 at 21:34

1

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

answered Nov 23 '14 at 21:34

ffflabs

17,166
5
51
77

WOOOOOOOOOOH . THATS IT. YOUR THE BEST. THANK YOU VERY MUCH . THIS PROBLEM IS 1 week OLD ~_~/ AND THEN AT LAST . YOU SOLVED IT :D THANK YOU VERY MUCH! – ronscript Nov 24 '14 at 00:55
i have question. what if i want to make radiant_team_id and dire_team_id dynamic? example i dont want to filter out using team id. – ronscript Nov 24 '14 at 01:16
It depends. A series identifies a unique team pair? Or it does address a stage that can apply to several team pairs? Beyond that, you need to consider how much logic will lie at the model level and how much can be managed with business logic. – ffflabs Nov 24 '14 at 01:23
btw. Thanks. my problem now is how can i cinvert this to laravel queries. :D – ronscript Nov 24 '14 at 01:32
I'm afraid I haven't ever used eloquent ORM, but it should be pretty straightforward – ffflabs Nov 24 '14 at 02:05
hi i updated below table. how do i join scheduled matches here. please see above updates – ronscript Nov 24 '14 at 14:34
I encourage you to create a new question, linking the prior related questions to provide context. – ffflabs Nov 24 '14 at 14:44
hi . here please take a look. http://stackoverflow.com/questions/27108029/how-to-left-join-on-multiple-columns-using-my-query – ronscript Nov 24 '14 at 15:13

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

2 Answers2