Mysql – Simple SELECT-query on big table gets stuck in “Sending data” state

indexMySQLoptimizationselect

I have a table with about ~200 million rows looking like this:

id, name, timestamp, class

I have an index on each column and I am trying to run a query like this:

SELECT name
FROM t
WHERE timestamp >= date_x AND
      timestamp < date_y AND
      class='class_a'

This query only uses the index on class and when date_x and date_y is about a month a part EXPLAIN says that it looks at about 12.5 M rows which is less than for the index on timestamp.

When the date range is small this query obviously returns fast but as the range increases this query soon gets stuck and never even starts sending any data (i.e stuck on Sending data), compared to a simple SELECT * FROM t which in SHOW PROCESS list alternates between Sending data and Writing to net.

My current workaround is to fetch rows within a daily range and then concatenate the results. For a month it should return about 150 K rows which I think should be doable for a straight-forward query such as this. That is why I think that there should be a way to do this without the workaround.

I have tried looking at the settings but have found no apparent mistakes there either. I can update this question with the relevant MySQL settings.

Do I need a multicolumn index on both class and timestamp?
Why cannot MySQL use both indexes to limit the search?
Have I missed something?

EXPLAIN output:

1   SIMPLE  table   ref index_class,index_timestamp index_class 302 const   11898928    100.00  Using where

Best Answer

I believe that for a small interval the index chosen by the query optimizer is the one that uses the timestamp, so it is faster.

I think a composite index class, timestamp would solve the problem.

Ps: Can you place the query plan when the range is small?

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.

Best Answer

Related Solutions

Mysql – EXPLAIN output suggests that the index is not being used

MySQL Query State – Troubleshooting Hangs on Sending Data

Refactor Your Query

Change the esp_gameplayer PRIMARY KEY

Give it a Try !!!

Related Question