How do I select all matches in a database that have two particular players?

The situation:

Matches have 10 players, 5 on each team. There are 100 players total and each match can have any player in any of the ten spots. I need to extract the following given two players:

1. How many matches were player A and player B allies?
2. How many of these did they win?
3. How many matches were player A and player B opponents?
4. How many of these did player A win?

My current method (too slow and possibly doesn’t work):

I’ve made three tables, “players”, “matches” and “matches_players” which maps the matches to the players that played in them. The column “winning_team” in matches represents which team won (0 = team A, 1 = team B) and column “position” in the matches_players table represents what spot the player was in the lineup. 0-4 is on team A, 5-9 is on team B.

---

players

---

player_id
43179582
63260623
31250276
54829050
22257854
etc...

---

matches

---

match_id     winning_team
95824317     0
06236326     0
02763125     1
90505482     0
78544325     1
etc...

---

matches_players

---

relation_id     match_id     player_id     position
1               95824317     43179582      1
2               95824317     63260623      5
3               06236326     43179582      7
4               06236326     54829050      0
5               06236326     22257854      4
etc...

Here’s the statement I’m using for finding match and win counts of heroes on the same team (after over 12 hours of surfing and trying to understand SQL):

SELECT
COUNT(*) AS match_count,
SUM(CASE WHEN team = winning_team THEN 1 ELSE 0 END) AS win_count
FROM
    (SELECT
    matches.match_id,
    (CASE WHEN position < 5 THEN 0 ELSE 1 END) AS team,
    winning_team
    FROM matches INNER JOIN matches_players ON matches.match_id = matches_players.match_id
    WHERE player_id = [____] OR player_id = [____]
    GROUP BY match_id, team HAVING COUNT(*) = 2)
AS pair_matches

It seems to work, but it’s incredibly slow (90+ secs on 250,000 matches) and ideally I’d like to have opposing team results in the same query (which should be faster than two separate queries, no?) Can the query be made faster/better? Is there a database design flaw?

I really appreciate any help guys. Advice in any area is welcome. Thanks.

Answer

I think this should do it or at least give you a hint

-- How many matches were player A and player B allies?
select DISTINCT (match_id) from MATCHES_PLAYERS where PLAYER_ID='43179582' or PLAYER_ID='63260623' group by MATCH_ID;

-- How many of these did they win?
select  PLAYER_ID, count(WINNING_TEAM)
from 
(select  PLAYER_ID, WINNING_TEAM, POsition from MATCHES_PLAYERS mp, matches m where mp.MATCH_ID=m.MATCH_ID and position<=4 and WINNING_TEAM=0
union all
select PLAYER_ID, WINNING_TEAM, POsition from MATCHES_PLAYERS mp, matches m where mp.MATCH_ID=m.MATCH_ID and position>4 and WINNING_TEAM=1)
group by PLAYER_id;

-- How many matches were player A and player B opponents?
select mp1.*, m.WINNING_TEAM, mp2.PLAYER_ID as opponent from MATCHES_PLAYERS mp1, MATCHES_PLAYERS mp2, matches m where mp1.MATCH_ID=m.MATCH_ID and  mp1.MATCH_ID=mp2.MATCH_ID and mp1.PLAYER_ID!=mp2.PLAYER_ID and (mp1.PLAYER_id in (43179582, 63260623) and mp2.player_id in (43179582, 63260623))
select DISTINCT mp1.MATCH_ID from MATCHES_PLAYERS mp1, MATCHES_PLAYERS mp2, matches m where mp1.MATCH_ID=m.MATCH_ID and  mp1.MATCH_ID=mp2.MATCH_ID and mp1.PLAYER_ID!=mp2.PLAYER_ID and (mp1.PLAYER_id in (43179582, 63260623) and mp2.player_id in (43179582, 63260623))

-- How many of these did player A win?
select
mp1.*, m.WINNING_TEAM, mp2.PLAYER_ID as opponent
from MATCHES_PLAYERS mp1, MATCHES_PLAYERS mp2, matches m
where mp1.MATCH_ID=m.MATCH_ID
and mp1.MATCH_ID=mp2.MATCH_ID
and mp1.PLAYER_ID!=mp2.PLAYER_ID
and
(
   mp1.PLAYER_id=43179582
   and mp2.player_id=63260623
)
and mp1.position<=4
and m.WINNING_TEAM=0