ruby on rails - Mysql query optimization -

I am working on a rail project and am killing a display problem. Here is the simplified database schema

 table gaming_platforms (~ 5 rows) ID table game (~ 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_pair (~ 1 rows) id gaming_plamat_id user name 

is now given a username, I want to display those players whose names match the input, without any They have played, have won or have lost

I have made 3 ideas to avoid the problem of 1 + n

 or as the place of online_player_games Please select online_player_id online_players.id, counting (*) as the game left the game from joining online_players at games.gaming_platform_id = online_players.gaming_platform_id and (games.black_id = online_players.id or the game .white_id = online_players .id) group by online_players.id; Or as online_player_id form, the online_players.id selection count (games.id) online_player_won_games left the game from online_players as a place to play games.gaming_platform_id = online_players.gaming_platform_id and ((games.winner = 1 more games. Black_id = joining the game at online_players.id) or (games.winner = 2 and games.white_id = online_players.id)) group by online_players.id; Or, as the online_player_id form, online_players.id selection count (games.id) online_player_lost_games left the game from online_players as a place of view games.gaming_platform_id = online_players.gaming_platform_id and ((games.winner = 2 more games. Black_id = joining the game at online_players.id) or (games.winner = 1 and games.white_id = online_players.id)) group by online_players.id; 

takes 20 seconds to query against those views, without using any index. The query looks complex I'm not sure what indexes should I make. Any opinions or suggestions are very welcome.

I think there are some general scalability problems with your design. As you add your winning count / loses will be enough work for your database from the perspective of an I / O.

I recommend creating a table called player_record which is only player ID, win, loss, then create a stored procedure that is said to be when the game ends ( Like spGameFinished (game_id, winner, ..); ) That is why the stored procedure will be responsible for performing any tasks required for a game that is completed, and from those tasks One is that the player_record to update On which the winner is called based. I am convinced that when the game starts, you play games with 2 player IDs, but if you do not, then you will have to take the stored procedure along with the completion of those games.

Once this happens, the queries that are needed to gather information about the loss of win / loss are small, it also allows you to manage the storage needs of the game different from players' records. (I.e. you can actually leave the old game without affecting users)