On 18 Jan 2011, at 13:51, Ian Hardingham wrote: > Hey guys. I am currently doing the following to find out the "record" > between two players in my game: > > SELECT count(*) TotalGames, sum(score > 0) GamesWonByPlayer1, sum(score > < 0) GamesWonByPlayer2, sum(score = 0) Draws FROM multiturnTable WHERE > complete=1 AND player1='Johnson' AND player2='Moonface''; > SELECT count(*) TotalGames, sum(score < 0) GamesWonByPlayer1, sum(score >> 0) GamesWonByPlayer2, sum(score = 0) Draws FROM multiturnTable WHERE > complete=1 AND player1= 'MoonFace' AND player2='Johnson'; > > (then adding them up in my client). > > I am wondering if, to optimise, I should replace this with a > recordAgainst table with a load of player,player pairs which is updated > whenever players play against each other.
Well: * Perhaps (if it's currently too slow). It wouldn't be my first choice of optimisation. * No (if it is currently fast enough) You could use a UNION to perform both selects in a single call, but this is unlikely to make much difference. You could create an index on multiturntable over (complete,player1,player2) if you don't have one already. You could run ANALYZE if you have some indexes and you haven't run it since populating the database. Best Regards, Phil Willoughby -- Managing Director, StrawberryCat Limited StrawberryCat Limited is registered in England and Wales with Company No. 7234809. The registered office address of StrawberryCat Limited is: 107 Morgan Le Fay Drive Eastleigh SO53 4JH _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
