Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For some reason, the query state hangs on "Sending Data" (using show processlist;) and I have no idea why. This is the table creating script:

CREATE TABLE IF NOT EXISTS `esp_game` (
  `gameID` int(10) unsigned NOT NULL,
  `gameType` tinyint(3) unsigned NOT NULL,
  `mapID` tinyint(3) unsigned NOT NULL,
  `createDate` int(11) NOT NULL,
  PRIMARY KEY (`gameID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `esp_gameplayer` (
  `gameID` int(11) unsigned NOT NULL,
  `summonerID` int(11) unsigned NOT NULL,
  `championID` int(11) NOT NULL,
  `teamID` int(11) NOT NULL,
  `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`gameID`,`summonerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `gameTypeMap` (
  `gameTypeID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `gameTypeName` varchar(50) NOT NULL DEFAULT '',
  `gameTypePortugueseDesc` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`gameTypeID`),
  UNIQUE KEY `gameTypeName` (`gameTypeName`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

This is the query that gets stuck:

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;

Extra info:

esp_game had about 10M entries (~1 Gig), esp_gameplayer about 100M (~7 Gigs) entries and gameTypeMap 10.

Explain before the deletion:

EXPLAIN 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;
+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows    | Extra          |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------+
|  1 | SIMPLE      | g     | ALL    | PRIMARY       | NULL    | NULL    | NULL                 | 7706280 | Using filesort |
|  1 | SIMPLE      | gt    | eq_ref | PRIMARY       | PRIMARY | 1       | teemo.g.gameType     |       1 |                |
|  1 | SIMPLE      | gp    | eq_ref | PRIMARY       | PRIMARY | 8       | teemo.g.gameID,const |       1 | Using index    |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------+
3 rows in set (0.00 sec)

Server configuration:

[mysqld]
(...)
innodb_buffer_pool_instances = 4
innodb_buffer_pool_size = 1536M
innodb_lock_wait_timeout = 25

The only way I've managed to make it work again is truncate everything from those tables - Thankfully I'm able to do that, but soon I won't be able to truncate everything anymore! Any Idea how to solve this problem?

share|improve this question
    
What does "getting stuck" and "sending data" mean? –  Mark Storey-Smith Dec 23 '13 at 1:03
    
Whenever I check the query states using "show processlist;", the state "Sending data" on the given query does not change. –  Honux Dec 23 '13 at 1:16

1 Answer 1

up vote 0 down vote accepted

First, look at the EXPLAIN plan again.

The first line says that the Query Optimizer to do the following:

  • Full table scan of at least 7,706,280 rows without using any keys (thus the root cause of the Sending data query state)
  • Each row in passes through from esp_game will do a lookup to esp_gameplayer and gameTypeMap

I would like for you to do the following:

Refactory Your Query

Your original query is:

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;

If the summonerID drives which gameIDs to retrieve, then the query needs to have esp_gameplayer be the lead table in the query

SELECT
    g.gameID, g.gameType, g.mapID,
    g.createDate, gt.gameTypePortugueseDesc
FROM
    (SELECT gameID FROM esp_gameplayer WHERE summonerID=401129) gp
    INNER JOIN esp_game g ON g.gameID = gp.gameID
    INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID
ORDER BY g.createDate DESC LIMIT 10;

Change the esp_gameplayer PRIMARY KEY

CREATE TABLE IF NOT EXISTS `esp_gameplayer` (
  `gameID` int(11) unsigned NOT NULL,
  `summonerID` int(11) unsigned NOT NULL,
  `championID` int(11) NOT NULL,
  `teamID` int(11) NOT NULL,
  `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`summonerID`,`gameID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

To reverse the PRIMARY KEY do the following:

CREATE TABLE IF NOT EXISTS `esp_gameplayer_new` (
  `gameID` int(11) unsigned NOT NULL,
  `summonerID` int(11) unsigned NOT NULL,
  `championID` int(11) NOT NULL,
  `teamID` int(11) NOT NULL,
  `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`summonerID`,`gameID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO esp_gameplayer_new SELECT * FROM esp_gameplayer;
ALTER TABLE esp_gameplayer RENAME esp_gameplayer_old;
ALTER TABLE esp_gameplayer_new RENAME esp_gameplayer;

Since you asked for a specific summonerID, reversing the PRIMARY KEY gathers all the corresponding gameIDs faster for summoner 401129

Make the changes, rerun the EXPLAIN plan. It should improve a great deal.

Give it a Try !!!

share|improve this answer
    
Thank you! Gonna try the refactor. Can't set the summonerID on esp_gameplayer as primary key since the summonerID repeats several times. –  Honux Dec 23 '13 at 1:40
    
You must reverse the PRIMARY KEY also –  RolandoMySQLDBA Dec 23 '13 at 1:42
    
I just added code to show how to reverse the PRIMARY KEY –  RolandoMySQLDBA Dec 23 '13 at 1:44
    
It worked! At least so far! :) –  Honux Dec 23 '13 at 3:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.