On 12/06/2011, at 9:59 PM, Ian Hardingham 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
> SELECT count(*) TotalGames
> , sum(score > 0) GamesWonByPlayer1
> , sum(score < 0) GamesWonByPlayer2
> , sum(score = 0) Draws
> FROM multiturnTable
> WHERE complete=1 AND player1 = '?' AND player2 = '?'
>
> SELECT count(*) TotalGames
> , sum(score < 0) GamesWonByPlayer1
> , sum(score > 0) GamesWonByPlayer2
> , sum(score = 0) Draws
> FROM multiturnTable
> WHERE complete=1 AND player1 = '?' AND player2 = '?'
You should have ? instead of '?', unless there's something unique about your
programming language.
> 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?)
Yes. Since your query asks for player1 = ? AND player2 = ?, then SQLite will
look for an index in that order, so should benefit greatly from an index on
(player1, player2). Since the query also has complete = 1, I think you should
change the order to match the index, ie:
where player1 = ? and player2 = ? and complete = 1
That way, SQLite will use the index on player1, player2, and then just filter
the remainder according to those that have complete = 1. There's no point
indexing "complete" since it only contains two distinct values. If anyone knows
SQLite's internal workings better and finds error with this summary, let us
know.
> and I know that I should be using ids rather than strings for players!
Well, if you know it, then do it ;-) It's likely to make a significant
difference to your queries and probably remove the need for you to have all
these statistics caching tables, not to mention ensuring internal consistency,
saving a lot of disk space and other overheads.
It shouldn't be very hard to do, at least for a time test. Just create another
table like this:
begin immediate
;
create table Player
( id integer primary key not null
, name text unique not null collate nocase
, other columns
)
;
create table multiturnTableNormalised
( id integer primary key not null
, player1 integer not null references Player(id)
, player2 integer not null references Player(id)
, other columns
)
;
create index multiturnTableNormalisedPlayers on multiturnTableNormalised
(player1, player2)
;
insert into Player (name)
select distinct player1 from multiturnTable
union
select distinct player2 from multiturnTable
;
insert into multiturnTableNormalised (id, player1, player2, other columns)
select rowid as id
, (select id from Player where name = player1) as player1
, (select id from Player where name = player2) as player2
, other columns
from multiturnTableNormalised
;
commit
;
then you can query, such as:
select count(*) as TotalGames
, sum(score > 0) as GamesWonByPlayer1
, sum(score < 0) as GamesWonByPlayer2
, sum(score = 0) as Draws
from multiturnTableNormalised
where player1 = (select id from Player where name = ?)
and player2 = (select id from Player where name = ?)
and complete = 1
> 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?
As I mentioned in my previous email, and implied above, I suggest that you do
some tests to see if you can do all of your queries live, rather than caching
the statistics. With integer key columns and good indexes, your speed may be
ample for what you need, and save a lot of caching and extra tables. If the
speed turns out to be too slow and you're sure that you've optimised the
schema, then at least you know you sure that the caching table are worth the
effort.
> vsRecordTable could easily have a million entries.
> 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.
I'll take you up on that offer. I rarely play a game more than once, but it
will give me a better idea of what you're actually doing.
Do you have an iPad/iPhone version?
Tom
BareFeetWare
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
