Stuck in optimizing query

[asmith]

Hello,

 

Using MySQL 5.1,

I have this games table which is storing info about games played: (100,000+ rows and growing.)

 

CREATE TABLE games (
  id_game int(10) unsigned NOT NULL auto_increment,
  info1 mediumint(9) NOT NULL,
  info2 tinyint(4) NOT NULL,
  versus_type tinyint(3) unsigned NOT NULL,
  post_time int(10) NOT NULL,
  game_type tinyint(4) NOT NULL,
  num_views mediumint( unsigned NOT NULL default '0',
  downloads mediumint( unsigned NOT NULL default '0',
  PRIMARY KEY  (id_game),
  KEY post_time (post_time),
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

 

and another table which is storing the players for each game: (+600,000 rows and growing.)

 

CREATE TABLE games_players (
  id_game int(11) NOT NULL,
  id_player mediumint( NOT NULL,
  `type` tinyint(3) unsigned NOT NULL,
  is_winner tinyint(1) unsigned NOT NULL default '0',
  KEY id_player (id_player),
  KEY id_game (id_game,id_player)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

Each game could have different number of players assigned to it.

So in a player page I wanna show his games normally. In an overall view and statistics, normally I gotta get all games to count different statistics which is all fine I guess. I use a query similar to this to get players games:

 

SELECT p2.*, g.rate1, g.rate2, g.post_time
FROM games_players AS p
INNER JOIN games_players AS p2 ON p.id_game = p2.id_game
INNER JOIN games AS g ON p.id_game = g.id_game
WHERE p.id_player = [id_player] AND p.type = 0 AND g.game_type = [x] AND g.position = 0
  ORDER BY post_time ASC, g.id_game ASC

 

The first join is to the same table to get other players our player played against.

The second join is to get the game info itself.

 

This query takes about average -0.05s to most members. But there are some members with like +5,000 games played and the query goes up to +0.3s getting those. This is the best I could get out of current situation.

I've tried separating games table from players. But then for the second query I have to put "WHERE id_game IN (+5000 ids here)" which kinda results the same.

I have to get all those rows, cause I gotta count different statistics vs each opponent and so and it is not possible to create another table to record each statistics vs every opponent in a separate record.

 

I'm willing to redesign my tables, modify my PHP codes, etc...

Am I getting the best out of it or it can be even better?

 

Thanks for your time.

p.s. I removed a few info columns in those tables just to simply reading it.

[fenway]

Sounds like you need a summary table to cache the calculation.

[asmith]

You're right. Even if there was a way to save me more time, there will come a time when they reach 10,000 games and more and I'll be back here again.

 

Thanks for the replay