I have this table set up in a database with results of games:
Table Players
id ... name
1 .... Alice
2 .... Bob
3 .... Charlie
... etc
Table Games
Player1 Player2 myscore oppscore result
1 ... 3 .... 25 ... 18 .... W
3 ... 2 .... 15 ... 20 .... L
2 ... 1 .... 17 ... 17 .... T
myscore refers to Player1, oppscore refers to Player2
I want a query that returns a player's most frequent opponents, along with the win-loss record between them. (I get the win-loss record with a second query on each opponent.)
So I use this:
SELECT count( * ) p2.name "Opponent",
FROM games, players p1, players p2
WHERE p1.name = ?
AND games.gametype = ?
AND games.player1 = p1.id
AND games.player2 = p2.id
GROUP BY player2, gametype
ORDER BY count( * ) DESC
In order to pick up all games (regardless of who is player1 and who is player2) I store every game TWICE: i.e. I really have:
Player1 Player2 myscore oppscore result
1 ... 3 .... 25 ... 18 .... W
3 ... 1 .... 18 ... 25 .... L
3 ... 2 .... 15 ... 20 .... L
2 ... 3 .... 20 ... 15 .... W
2 ... 1 .... 17 ... 17 .... T
1 ... 2 .... 17 ... 17 .... T
I would like to eliminate that redundancy of the data, thereby reducing the database size by half.
I tried this (where g1 is a table like games, but with the redundant rows eliminated).
create view gv as
select * from g1
union
select
player2 player1,
player1 player2,
(case when result = 'T' then 'T'
when result = 'W' then 'L'
when result = 'L' then 'W'
end) result,
oppscore myscore,
myscore oppscore
from g1
And then doing my query against gv instead of against games.
Which works ... except that it takes (based on one example), more than 10 times as long (0.10 seconds for games, vs 1.4 seconds for gv).
Is there a better way to do this?
