I have a huge bottle neck on my Game Server for the following query which is used to store the current leaderboard.
I am currently only calling this query via cron once every 5 mins, but would love to optimise it enough to be called every minute or when needed.
The query is taking 30 seconds and currently only ~2000 users and 7000 games played (stored in Games and TopPlayerScores). I'm afraid it will only get worse!! Please Help me Overflow-Kenobi! Your my only hope!
SET @rank=0;
INSERT INTO Board (TopScorePKID, GamePKID, UserPKID, UniquePlayerID, PlayerName, TopPlayerScore, Position, Date)
(SELECT bad.ID AS TopScorePKID, bad.GamePKID, bad.UserPKID, bad.UniquePlayerID, bad.PlayerName, bad.TopPlayerScore, @rank:=@rank+1 AS Position, bad.Date
FROM (
SELECT g.GamePKID, g.TopPlayerScore, l.ID, l.UserPKID, u.UniquePlayerID, u.PlayerName, (l.Date) AS Date
FROM Games g, TopPlayerScores l, UserDetails u
WHERE l.GamePKID = g.GamePKID
AND u.UserPKID = l.UserPKID
AND u.SECRET_DETAIL = 0
AND g.TopPlayerScore >= (SELECT DISTINCT k.TopPlayerScore AS Highest
FROM Games k, TopPlayerScores t
WHERE t.UserPKID = l.UserPKID
AND k.GamePKID = t.GamePKID
ORDER BY k.TopPlayerScore DESC
LIMIT 1)
GROUP BY l.UserPKID
ORDER BY g.TopPlayerScore DESC, Date ASC)
AS bad);
Please someone help!! Should I break it up onto views? Or use Inner Join keywords? What is the best approach?
Thanks so much for even looking at this mess :D!
UPDATED 1.0 : EXPLAIN EXTENDED Results:
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY ALL NULL NULL NULL NULL 1521 100.00 2 DERIVED l ALL NULL NULL NULL NULL 6923 100.00 Using temporary; Using filesort 2 DERIVED u eq_ref PRIMARY PRIMARY 4 DBNAME.l.UserPKID 1 100.00 Using where 2 DERIVED k eq_ref PRIMARY PRIMARY 4 DBNAME.l.GamePKID 1 100.00 Using where 3 DEPENDENT SUBQUERY t ALL NULL NULL NULL NULL 6923 100.00 Using where; Using temporary; Using filesort 3 DEPENDENT SUBQUERY g eq_ref PRIMARY PRIMARY 4 DBNAME.t.GamePKID 1 100.00 Using where
UPDATED 2.0: Limited Schema for querying tables
Using the Games to store game scores and other information about a game
`Games` (
`GamePKID` int(11) NOT NULL AUTO_INCREMENT,
`TopPlayerScore` int(11) NOT NULL,
`OTHER_MISC_STUFF_REMOVED` int(11) NOT NULL
PRIMARY KEY (`GamePKID`)
)
Using the following to link the user to the Game and store the time/date
`TopPlayerScores` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`UserPKID` int(11) NOT NULL,
`GamePKID` int(11) NOT NULL,
`Date` datetime NOT NULL,
PRIMARY KEY (`ID`)
)
Used to store each unique player
`UserDetails` (
`UserPKID` int(11) NOT NULL AUTO_INCREMENT,
`UniquePlayerID` char(40) NOT NULL,
`PlayerName` char(96) NOT NULL,
`SECRET_DETAIL` tinyint(1) NOT NULL DEFAULT '0',
`isPlayer` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`UserPKID`)
)