[MySQL] optimize query or possibly redesign tables

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.