Hello,

I have a query which is taking 3-5 seconds. I'd appreciate some help.

MySQL 5.5
I have a sports games table that stores information about games played. It looks like this: (I cut off extra data columns to simplify table)

Reaching 150,000 rows and growing.
Code:
CREATE TABLE IF NOT EXISTS `games` (
  `id_game` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sport` smallint(8) NOT NULL,
  `type` tinyint(4) NOT NULL,
  `versus` tinyint(3) unsigned NOT NULL,
  `submit_time` int(10) NOT NULL,
  `position` int(11) NOT NULL,
  `points1` mediumint(9) NOT NULL,
  `points2` mediumint(9) NOT NULL,
  `views` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `downloads` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `reporter_ip` varchar(255) NOT NULL,
  PRIMARY KEY (`id_game`),
  KEY `submit_time` (`submit_time`),
  KEY `position` (`position`),
  KEY `type` (`type`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
and players table, which stores which players have played those games:

About 700,000 rows and growing.
Code:
CREATE TABLE IF NOT EXISTS `players` (
  `id_game` int(11) NOT NULL,
  `id_player` varchar(80) NOT NULL,
  `player_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`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
The plan is simply doing pagination for showing recent games played by some player, showing 50 games per page sorting by games.submit_time because the order of games may change after submitting. So the submit_time is something to rely on, not id_game column.

Here's the query: (takes about 0.02s)
Code:
SELECT g.id_game, sport ...
FROM players AS p
   INNER JOIN games AS g ON p.id_game = g.id_game
WHERE p.id_player = 170 AND p.player_type = 2  AND g.type IN (-1, -2, -3, -4)
ORDER BY g.submit_time DESC
LIMIT 0, 50
EXPLAIN on that query (thought It might be useful)
Code:
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	g 	index 	PRIMARY,type 	submit_time 	4 		8 	Using where
1 	SIMPLE 	p 	ref 	id_player,id_game 	id_game 	4 	g.id_game 	7 	Using where
Now for pagination, I need to know all the rows. The original query had SQL_CALC_FOUND_ROWS, but that was causing much more delay. So the "total rows" query: (takes about 3 seconds!!)

Code:
SELECT COUNT(*)
FROM players AS p
   INNER JOIN games AS g ON p.id_game = g.id_game
WHERE p.id_player = 170 AND p.player_type = 2  AND g.type IN (-1, -2, -3, -4)
The EXPLAIN on the last query:
Code:
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	g 	ALL 	PRIMARY,type 				143829 	Using where
1 	SIMPLE 	p 	ref 	id_player,id_game 	id_game 	4 	g.id_game 	7 	Using where
The important point is that I have to inner join players table to games table because sometimes I need to count the games with specific options. Can't just count rows from players table. (Some of players have played over 12000 games)
If you think I have to redesign my tables and data, I can do that. So I'd welcome any good solution.

Thanks for your time.