Hi Phil, thanks for the information. In general, my server is too slow. It has to run many operations a second, and many DB operations, so the exact definition of "too slow" is a little arbitrary.
I'm intruiged that you feel a pre-computed table is not a good idea. Is there an expectation that that would not be especially faster than my current method? Thanks, Ian On 18/01/2011 14:07, Philip Graham Willoughby wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
