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.