How to optimize this user ranking query
- by James Simpson
I have 2 databases (users, userRankings) for a system that needs to have rankings updated every 10 minutes. I use the following code to update these rankings which works fairly well, but there is still a full table scan involved which slows things down with a few hundred thousand users.
mysql_query("TRUNCATE TABLE userRankings");
mysql_query("INSERT INTO userRankings (userid) SELECT id FROM users ORDER BY score DESC");
mysql_query("UPDATE users a, userRankings b SET a.rank = b.rank WHERE a.id = b.userid");
In the userRankings table, rank is the primary key and userid is an index. Both tables are MyISAM (I've wondered if it might be beneficial to make userRankings InnoDB).
