Er74SGFp

1CREATE TABLE IF NOT EXISTS `esp_game` (
2  `gameID` int(10) unsigned NOT NULL,
3  `gameType` tinyint(3) unsigned NOT NULL,
4  `mapID` tinyint(3) unsigned NOT NULL,
5  `createDate` int(11) NOT NULL,
6  PRIMARY KEY (`gameID`)
7) ENGINE=InnoDB DEFAULT CHARSET=latin1;
8
9CREATE TABLE IF NOT EXISTS `esp_gameplayer` (
10  `gameID` int(11) unsigned NOT NULL,
11  `summonerID` int(11) unsigned NOT NULL,
12  `championID` int(11) NOT NULL,
13  `teamID` int(11) NOT NULL,
14  `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
15  PRIMARY KEY (`gameID`,`summonerID`)
16) ENGINE=InnoDB DEFAULT CHARSET=latin1;
17
18CREATE TABLE IF NOT EXISTS `gameTypeMap` (
19  `gameTypeID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
20  `gameTypeName` varchar(50) NOT NULL DEFAULT '',
21  `gameTypePortugueseDesc` varchar(50) NOT NULL DEFAULT '',
22  PRIMARY KEY (`gameTypeID`),
23  UNIQUE KEY `gameTypeName` (`gameTypeName`)
24) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
25	
26SELECT g.gameID, g.gameType, g.mapID, g.createDate, gt.gameTypePortugueseDesc FROM esp_game g INNER JOIN esp_gameplayer gp ON g.gameID = gp.gameID and gp.summonerID=401129 INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID ORDER BY g.createDate DESC LIMIT 10;
27	
28EXPLAIN SELECT g.gameID, g.gameType, g.mapID, g.createDate, gt.gameTypePortugueseDesc FROM esp_game g INNER JOIN esp_gameplayer gp ON g.gameID = gp.gameID and gp.summonerID=401129 INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID ORDER BY g.createDate DESC LIMIT 10;
29+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------+
30| id | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows    | Extra          |
31+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------+
32|  1 | SIMPLE      | g     | ALL    | PRIMARY       | NULL    | NULL    | NULL                 | 7706280 | Using filesort |
33|  1 | SIMPLE      | gt    | eq_ref | PRIMARY       | PRIMARY | 1       | teemo.g.gameType     |       1 |                |
34|  1 | SIMPLE      | gp    | eq_ref | PRIMARY       | PRIMARY | 8       | teemo.g.gameID,const |       1 | Using index    |
35+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------+
363 rows in set (0.00 sec)
37	
38[mysqld]
39(...)
40innodb_buffer_pool_instances = 4
41innodb_buffer_pool_size = 1536M
42innodb_lock_wait_timeout = 25
43	
44SELECT
45    g.gameID, g.gameType, g.mapID,
46    g.createDate, gt.gameTypePortugueseDesc
47FROM
48    esp_game g
49    INNER JOIN esp_gameplayer gp ON g.gameID = gp.gameID and gp.summonerID=401129
50    INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID
51ORDER BY g.createDate DESC LIMIT 10;
52	
53SELECT
54    g.gameID, g.gameType, g.mapID,
55    g.createDate, gt.gameTypePortugueseDesc
56FROM
57    (SELECT gameID FROM esp_gameplayer WHERE summonerID=401129) gp
58    INNER JOIN esp_game g ON g.gameID = gp.gameID
59    INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID
60ORDER BY g.createDate DESC LIMIT 10;
61	
62CREATE TABLE IF NOT EXISTS `esp_gameplayer` (
63  `gameID` int(11) unsigned NOT NULL,
64  `summonerID` int(11) unsigned NOT NULL,
65  `championID` int(11) NOT NULL,
66  `teamID` int(11) NOT NULL,
67  `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
68  PRIMARY KEY (`summonerID`,`gameID`)
69) ENGINE=InnoDB DEFAULT CHARSET=latin1;
70	
71CREATE TABLE IF NOT EXISTS `esp_gameplayer_new` (
72  `gameID` int(11) unsigned NOT NULL,
73  `summonerID` int(11) unsigned NOT NULL,
74  `championID` int(11) NOT NULL,
75  `teamID` int(11) NOT NULL,
76  `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
77  PRIMARY KEY (`summonerID`,`gameID`)
78) ENGINE=InnoDB DEFAULT CHARSET=latin1;
79INSERT INTO esp_gameplayer_new SELECT * FROM esp_gameplayer;
80ALTER TABLE esp_gameplayer RENAME esp_gameplayer_old;
81ALTER TABLE esp_gameplayer_new RENAME esp_gameplayer;