The profiling shows the naïve strategy works well only if the leaderboard page being generated is close to the front--that is, if people are interested in the highest-ranking gamers. If your application follows that pattern, you should optimize for it, but it's not always the case. Assume the gaming site doesn't follow this pattern, because all the low-ranked gamers are vain and want to check their rankings all the time. Now you have to optimize for the pages buried low in the ranks.
In any case, the profiling results are encouraging that I'm going in a good direction with the ranking columns, but benchmarking is crucial.
I ran benchmarks to show how the queries perform. The benchmarks randomly choose and request leaderboard pages with the same queries I've shown you. I'm including the benchmarking scripts in the code you can download from this article.
I benchmarked against the data I gave you, with 100,000 rows in the score tables. This load is entirely CPU-bound on my single-disk, single-CPU home computer.
I used innotop to watch which queries ran slowly. It was clearly the
To simulate concurrent updates, I added threads that choose a random score and change it. In designs #2 and #3, this means not only changing the score, but updating the rank column, too. I did not batch updates to ranks; I simply updated the affected open-ended range every time, so there's still a lot of room for optimization here.
I averaged across five runs for each level of concurrency, from one to twenty concurrent threads. Each thread generated 100 leaderboards. Here are the benchmark results, in leaderboards per second by number of concurrent requests:
|Read Threads||Design #1||Design #2||Design #3|
|Reads Only||1 Writer||5 Writers||Reads Only||1 Writer||5 Writers||Reads Only||1 Writer||5 Writers|
Look how much better design #3 performs! This vindicates my hunch that the key to good performance is getting rid of queries that scan lots of rows.