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