Guys, my apologies for spamming the list today.
A topic I've talked about before, but am just revisiting.
I often need to get the "record" between two people - how many games
they've won and lost against each other. For reference, the query is at
the end of the email. Once again, multiturnTable has a million rows, I
have separate indexes on complete and player1 and player2 (should I
also add an index on player1, player2?), and I know that I should be
using ids rather than strings for players!
Anyway, my question is - should I have a vsRecordTable which stores all
of these and updates on match completion, or should I calculate each
time I need it?
vsRecordTable could easily have a million entries.
Thanks guys,
Ian
PS - you guys have been fantastically helpful to me during the course of
development of Frozen Synapse, and if anyone is at all interested in the
game I'd love to give you a free copy as a (nowhere near good enough)
thank you.
%r = db.query("SELECT count(*) TotalGames, sum(score > 0)
GamesWonByPlayer1, sum(score < 0) GamesWonByPlayer2, sum(score = 0)
Draws FROM multiturnTable WHERE complete=1 AND player1 = '?' AND player2
= '?'", 0, %username, %opp);
%r2 = db.query("SELECT count(*) TotalGames, sum(score < 0)
GamesWonByPlayer1, sum(score > 0) GamesWonByPlayer2, sum(score = 0)
Draws FROM multiturnTable WHERE complete=1 AND player1 = '?' AND player2
= '?'", 0, %opp, %username);
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
