2

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?

2
  • What does "getting stuck" and "sending data" mean? Commented Dec 23, 2013 at 1:03
  • Whenever I check the query states using "show processlist;", the state "Sending data" on the given query does not change.
    – Honux
    Commented Dec 23, 2013 at 1:16

3 Answers 3

3

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

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

I had a similar issue on queries that had previously worked fine and found damage to the underlying filesystem. If you are having this issue it's wise to ensure that the issue is not external to the db engine itself.

0

SENDING DATA is an indication for a long-running query and as such is very bad.

Use EXPLAIN to find out what the database engine does and why it does not use an appropriate index.

If there is none, create one modeled after the query in case. Use EXPLAIN again to see if the engine uses this index. If not, you may use a hint like USE INDEX(myindex) or FORCE INDEX (myindex). The result should be stunning.

If not, your index might not be as good as you think. Reiterate the whole process.

Remember, though, if you change the query in case this index might no longer be appropriate, so again you have to reiterate.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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