Selecting the latest record for each player and ordering (performance issue)

Question

I have a table with ~300,000 records in it and I would like to select the latest record for each player and order it by rating. The following query gives me the desired results but it takes ~100s to run on the table with 300,000 records and the table will grow to many millions of records.

SELECT *
FROM players a
JOIN (
    SELECT name, server, Max(timestamp) AS MaxTimeStamp
    FROM players
    GROUP BY name, server
) b
ON a.name = b.name
AND a.server = b.server
AND a.timestamp = b.MaxTimeStamp
ORDER BY score desc

Execution plan looks like this:

id  select_Type table           type    possible_keys  key      key_length  ref                             rows    extra
1   PRIMARY     <derived2>      ALL                                                                         268683  Using temporary; Using filesort
1   PRIMARY     a               eq_ref  PRIMARY        PRIMARY  147         b.MaxTimeStamp,b.server,b.name  1   
2   DERIVED     players_temp    index                  PRIMARY  147                                         264813  Using index; Using temporary; Using filesort

Primary keys are 'timestamp', 'server', 'name'. 'score' is indexed Let me know if there is any more information that could help. Thanks very much!

EDIT

Create table statement for how the table currently is (i've change it a number of times!) is as follows:

    CREATE TABLE `players` (
      `timestamp` datetime NOT NULL,
      `server` varchar(25) NOT NULL,
      `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
      `normName` varchar(20) DEFAULT NULL,
      `position` varchar(20) DEFAULT NULL,
      `team` varchar(10) DEFAULT NULL,
      `won` smallint(6) DEFAULT NULL,
      `lost` smallint(6) DEFAULT NULL,
      `score` smallint(6) DEFAULT NULL,
      `picture` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`timestamp`,`server`,`name`),
      KEY `normName` (`normName`) USING BTREE,
      KEY `score` (`score`) USING BTREE,
      KEY `playerRecord` (`timestamp`,`server`,`name`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

innodb_buffer_pool_size has been set to 1GB, still seeing poor performance. The subquery alone takes 20 seconds to run. I did an explain on it:

EXPLAIN 
SELECT name, server, Max(timestamp) AS MaxTimeStamp
FROM players_temp
GROUP BY name, server

result:

1   SIMPLE  players_temp    index       rating2v2   3       265910  Using index; Using temporary; Using filesort

EDIT 2

I got a bit further by altering the GROUP BY in my subquery. I changed it from 'GROUP BY name, server' to 'GROUP BY timestamp, server, name' to match the order of the keys and now its using index instead of filesort temporary table, but there is still a problem and it seems to be ordering by score.

If I run this query:

SELECT *
FROM players_temp a
ORDER BY a.score

it takes 33 seconds to run and EXPLAIN indicates it's using filesort. There is a BTREE index on score, as indicated by above CREATE statement. Any ideas? This is the last hurdle, I believe, to getting the query to run properly. Thanks again

Answer 1

There is nothing wrong with a little selective denormalization of your data. If you've gone as far as you can with indexes & sub query logic, you could add a column that indicates which record is the latest for each player then add an index that includes that new column.

If your app knows which record is newest at the time of writing the record (i.e. the one being written is by definition the latest?) this is easy to do.

And if for some reason that can't be done, you could have a separate process examine the data from time to time and flag the latest records.

Complete normalization is great in terms of data integrity, but data eventually gets large enough that the trade off isn't worth it. All the big online services (Facebook, Instagram etc) are heavily denormalized.

Answer 2

There is no much else I can think of doing to get that result more efficiently. The subquery seems well suited because it shrinks the resultset and it's not dependant on columns of the main query.

Remember to have an index on the columns name, server and timestamp.

If this does not work with acceptable performance, you can try to run the subquery alone with EXPLAIN.

If it ends using filesort at some point, probably the keys don't fit the available memory. You can try to enlarge key_buffer_size if you are using MyISAM as engine, or innodb_buffer_pool_size if the table uses InnoDB, on the mysql configuration file. Please note that if your table keeps growing, at some point you will have the same problem again and you will have to look for another solution (or buy more memory, hehe).