Mysql query optimization

Question

Hi,

I am working on a Rails project and hitting a performance problem. Here is the simplified db schema

table gaming_platforms ( ~5 rows)
  id

table games ( ~10k rows)
  id
  gaming_platform_id
  winner             (black or white or n/a)
  black_id           => online_players.id
  white_id           => online_players.id

table online_players ( ~1k rows)
  id
  gaming_platform_id
  username

Now given a username, I want to display players whose names match the input, with no. of games they played, won or lost.

I created 3 views to avoid 1+n problem.

create or replace view online_player_games as
      select online_players.id as online_player_id, count(*) as games 
      from games
      left join online_players 
      on games.gaming_platform_id = online_players.gaming_platform_id and 
      (games.black_id = online_players.id or games.white_id = online_players.id)
      group by online_players.id;

create or replace view online_player_won_games as
      select online_players.id as online_player_id, count(games.id) as games 
      from online_players 
      left join games on games.gaming_platform_id = online_players.gaming_platform_id and 
      ((games.winner = 1 and games.black_id = online_players.id) or (games.winner = 2 and games.white_id = online_players.id))
      group by online_players.id;

create or replace view online_player_lost_games as
      select online_players.id as online_player_id, count(games.id) as games 
      from online_players
      left join games on games.gaming_platform_id = online_players.gaming_platform_id and 
      ((games.winner = 2 and games.black_id = online_players.id) or (games.winner = 1 and games.white_id = online_players.id))
      group by online_players.id;

Without using any index, it takes > 20 secs to query against those views. The query looks complicated. I am not sure which indexes I should create. Any opinions or suggestions are very welcome.

Answer 1

I think you have some general scalability issues with your design. As you add games and users the way you're counting up their wins/loses will become significantly more work for your database from an I/O perspective.

I would suggest creating a table called player_record which is simply the players ID, WINS, LOSSES. Then create a stored procedure that is called when a game finishes. ( say spGameFinished(game_id, winner,..); ) That stored procedure would be responsible for doing any tasks needed for a game that has completed, and one of those tasks is to update that player_record table based on whom is stated to be the winner. I'm assuming when the game starts you put the game in with the 2 player ids, but if you don't then the stored procedure would have to take those in as well at the completion of the game.

Once this is in place, the queries needed for gathering win/loss information is trivial. This also allows you to manage the storage requirements of the games separate from the players records. (i.e. you can drop games that are really old without affecting a users record)

Answer 2

I have created a new table online_player_stats with columns: online_player_id, games_as_black, games_won_as_black, games_lost_as_white, ...

I have also created increment_online_player_stats and decrement_online_player_stats stored procs which are called by insert/update/delete triggers.

I plan to create another stored procedure to update all players' stats in one shot to bootstrap and to clean up if something goes wrong.

Is this sounds a reasonable solution?