CODEWITHSUNDEEP
mysqlsql
willthiswork89
willthiswork89

Reputation: 174

Need help optimizing

I have a MySQL query that I'd like help optimizing. Its rather simple in nature but its taking about 0.5 seconds to run and this is going to be run very often.

SET @RankNumber:=0; 
SELECT NumberRank,TankName, Color
FROM
(
   SELECT @RankNumber := @RankNumber + 1 AS NumberRank, TankName, Color 
   FROM
   (
      SELECT TankID, MapID, Color, TankName
      FROM MAPDATA
      INNER JOIN Tank ON Tank.ID = MapData.TankID
      WHERE MapID = 3
      ORDER BY Rank DESC, TotalPP DESC
   ) Query1
) final
WHERE Color = 3
LIMIT 10;

Essentially what I'm doing, is im giving each record a number (RankNumber) and then I'm selecting all of the tanks from the database where the map is 3, then I give each of them the number in the map orders by rank and experience points. THEN I filter out all except the color I want and this is why I have the three levels essentially so I can get each line.

You have top 10, which is simple 1-10 then you have top purple, which may look more like 1,7,10,25,36 etc etc since the top 10 aren't the top 10 of all colors. Make sense? This is taking .5 seconds to complete and its hit by people probably 10-30 times a minute. 

1   PRIMARY <derived2>  ALL                 31220   Using where
2   DERIVED <derived3>  ALL                 31220   
3   DERIVED Tank    ALL PRIMARY,idx_TankID              9863    Using temporary; Using filesort
3   DERIVED MAPDATA ref PRIMARY,MapID,TankID,idx_mapdata    PRIMARY 8   microtanksbeta.Tank.ID  1

Here is my EXPLAIN on that query. Any help would be appreciated as to speeding this up. If there is a way i can tell it to keep this query in memory or something that works too just something so its not so slow.

SHOW CREATE TABLES

'MapData', 'CREATE TABLE `mapdata` (
  `MapID` int(11) NOT NULL,
  `TankID` int(11) NOT NULL,
  `Color` tinyint(4) NOT NULL,
  `X` int(11) DEFAULT ''-1'',
  `Y` int(11) DEFAULT ''-1'',
  `Rank` tinyint(4) NOT NULL DEFAULT ''0'',
  `Health` int(11) NOT NULL DEFAULT ''1000'',
  `Armors` tinyint(4) NOT NULL DEFAULT ''0'',
  `Duals` tinyint(4) NOT NULL DEFAULT ''0'',
  `Missiles` tinyint(4) NOT NULL DEFAULT ''0'',
  `Homings` tinyint(4) NOT NULL DEFAULT ''0'',
  `Radars` tinyint(4) NOT NULL DEFAULT ''0'',
  `Beacons` tinyint(4) NOT NULL DEFAULT ''0'',
  `HasRankKill` bit(1) NOT NULL DEFAULT b''0'',
  `TotalPP` bigint(20) NOT NULL DEFAULT ''0'',
  `RankPP` bigint(20) NOT NULL DEFAULT ''0'',
  `KillCount` int(11) NOT NULL DEFAULT ''0'',
  `DeathCount` int(11) NOT NULL DEFAULT ''0'',
  `TimePlayed` time NOT NULL DEFAULT ''00:00:00'',
  `EnabledEquipment` tinyint(4) NOT NULL DEFAULT ''0'',
  `Prestige` tinyint(4) NOT NULL DEFAULT ''0'',
  PRIMARY KEY (`MapID`,`TankID`,`Color`),
  KEY `MapID` (`MapID`),
  KEY `TankID` (`TankID`),
  KEY `idx_mapdata` (`MapID`,`Color`,`TankID`),
  CONSTRAINT `mapdata_ibfk_1` FOREIGN KEY (`MapID`) REFERENCES `maps` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `mapdata_ibfk_2` FOREIGN KEY (`TankID`) REFERENCES `tank` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1'


'Tank', 'CREATE TABLE `tank` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Username` text NOT NULL,
  `TankName` text NOT NULL,
  `KillAward` int(11) DEFAULT ''0'',
  `DeathAward` int(11) DEFAULT ''0'',
  `TimeAward` int(11) DEFAULT ''0'',
  `Stars` int(11) DEFAULT ''0'',
  `Trophy` int(11) DEFAULT ''0'',
  `Dot` int(11) DEFAULT ''0'',
  `Cartographer` int(11) DEFAULT ''0'',
  `OtherAwards` int(11) DEFAULT ''0'',
  `Banned` bit(1) DEFAULT b''0'',
  `BanExpiration` datetime DEFAULT NULL,
  `BanExplination` text,
  `Deleted` bit(1) DEFAULT b''0'',
  `DeletedDate` datetime DEFAULT NULL,
  `CreationDate` datetime NOT NULL DEFAULT ''2011-05-24 00:00:00'',
  PRIMARY KEY (`ID`),
  KEY `idx_TankName` (`TankName`(40)),
  KEY `idx_UNTankName` (`Username`(60),`TankName`(40)),
  KEY `idx_TankID` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=23239 DEFAULT CHARSET=latin1'

Upvotes: 1

Views: 104

Answers (2)

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39961

I don't see any use for 3 levels, 2 should be enough. Also I've moved the join out of the inner select to make that one faster.

SET @RankNumber:=0; 
SELECT inner.NumberRank, tank.TankName, inner.Color
 FROM (SELECT @RankNumber := @RankNumber + 1 AS NumberRank, TankID, Color
         FROM MAPDATA
        WHERE MapID = 3
        ORDER BY Rank DESC, TotalPP DESC
      ) inner
INNER JOIN Tank ON Tank.ID = inner.TankID
WHERE inner.Color = 3
LIMIT 10;

Make sure that the inner query alone uses a index and is fast. Try that and see if you get the same result and if it's faster.

Upvotes: 1

newtover
newtover

Reputation: 32094

SELECT NumberRank, TankName, Color
FROM (
    SELECT @RankNumber := @RankNumber + 1 AS NumberRank, TankID, Color
    FROM MAPDATA, (SELECT @RankNumber:=0) as init
    WHERE MapID = 3
    HAVING Color = 3
    ORDER BY Rank DESC, TotalPP DESC
    LIMIT 10
) ranks JOIN Tank ON Tank.ID = TankID

Upvotes: 0

Related Questions