Many thanks Igor.
Would this index be enough:
CREATE INDEX IF NOT EXISTS mtTablePlayer1 ON multiturnTable
(player1,player2)
Or do I need the opposite as well?
CREATE INDEX IF NOT EXISTS mtTablePlayer1 ON multiturnTable
(player2,player1)
Why I have two queries: the order of the arguments are different - I am
looking for games where player1 is Ian and player2 is Igor, but I also
want games where player1 is Igor and player2 is Ian.
Cheers,
Ian
On 12/06/2011 15:28, Igor Tandetnik wrote:
> Ian Hardingham<i...@omroth.com> 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.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
