mysql - SQL Query performance -

i have table set in database 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 player1, oppscore refers player2

i want query returns player's frequent opponents, along win-loss record between them. (i win-loss record second query on each opponent.)

so use this:

select count( * ) p2.name "opponent",  games, players p1, players p2 p1.name = ? , games.gametype = ? , games.player1 = p1.id , games.player2 = p2.id  group player2, gametype order count( * ) desc 

in order pick games (regardless of player1 , player2) store every game twice: i.e. 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 eliminate redundancy of data, thereby reducing database size half.

i tried (where g1 table games, redundant rows eliminated).

create view gv    select * g1 union    select     player2 player1,    player1 player2,    (case when result = 't' 't'          when result = 'w' 'l'          when result = 'l' 'w'            end) result,    oppscore myscore,    myscore oppscore    g1 

and doing query against gv instead of against games.

which works ... except takes (based on 1 example), more 10 times long (0.10 seconds games, vs 1.4 seconds gv).

is there better way this?

i think of views convenience, , unions slow. add them together, , conveniently slow. ok, on generalization.

what performance can live with?

denormalized (redundant , flipped in case) data has benefits namely speed @ expense of wasted space. it's juggling act.

one thing view union of 2 table scans there no filter. gets worse add scores. utilize no index.

are in need of looking @ data when have stored proc in parameters focusing on indexed player id's self join or like?

indexes can best friend this. running queries thru mysql explain can help.

anyway hope helpful in small way.