MySQL Query State – Troubleshooting Hangs on Sending Data

innodbMySQLmysql-5.5performancequery-performance

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?

Best Answer

First, look at the EXPLAIN plan again.

The first line says that the Query Optimizer will 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 you to try the following:

Refactor 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 !!!

Related Question