Ian Hardingham <[email protected]> wrote:
> 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!
Yes, an index on (player1, player2) would help. Once you have this index, I
don't think the query would be noticeably slower than selecting from a
dedicated table with running totals (but of course you can, and probably
should, test it).
> %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);
Why do you need two? It seems that the only difference between them is the
order of columns in SELECT clause.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
