Hey guys. I am currently doing the following to find out the "record"
between two players in my game:
SELECT count(*) TotalGames, sum(score > 0) GamesWonByPlayer1, sum(score
< 0) GamesWonByPlayer2, sum(score = 0) Draws FROM multiturnTable WHERE
complete=1 AND player1='Johnson' AND player2='Moonface'';
SELECT count(*) TotalGames, sum(score < 0) GamesWonByPlayer1, sum(score
> 0) GamesWonByPlayer2, sum(score = 0) Draws FROM multiturnTable WHERE
complete=1 AND player1= 'MoonFace' AND player2='Johnson';
(then adding them up in my client).
I am wondering if, to optimise, I should replace this with a
recordAgainst table with a load of player,player pairs which is updated
whenever players play against each other.
Some facts:
- multiturnTable has 100,000 - 10,000,000 rows
- number of users is between 10,000 and... well, 1,000,000 I suppose.
- the record between two players needs to be found very regularly
- I can't change the structure of multiturn table to have player1,
player2 in alphabetical order at this stage unfortunately (this would
remove the need to call the above query twice).
Any help is much appreciated.
Thanks,
Ian
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
