When I worked as a server engineer for a game company, I spent some significant time dealing with leaderboards (a.k.a. high score lists).  And lo and behold, when I started consulting and was asked to build a fantasy stock/options/futures trading site, there I was coding leaderboards again.

With a small number of people, of course, it's not a big deal to generate leaderboards on the fly.  But if your site is lucky enough to become successful, you don't want your database queries to bring your site to its knees.  Things also get more complicated if you want to implement "around me" leaderboards which show the current user's position with a few people above and below.

Leaderboard Tables

The first step is to create separate tables specifically for the leaderboards, rather than expecting to use other tables (e.g. user, team, or network tables).  Periodically, do a query from the source table sorting by score.  After clearing out the leaderboard table, the results can be inserted with the primary key being position in the table.  This makes it easy to look up a particular user to find their position and then do an "around me" query.

Doing a delete all from the table followed by an insert (all within a transaction) will work up to a certain point.  But when you're pushing hundreds of thousands of users that will likely be a performance issue.  An alternate approach is to create a new temporary table, do the inserts, then—in a new transaction—delete the old table and rename the temporary table to replace it.

Fragment Caching

I wanted to share one other trick I was able to use in my latest project.  I wanted to cache the HTML fragments showing the leaderboards, but also wanted the current user to see themselves highlighted in the table (meaning the view would look different to different users).  That would seem to eliminate the possibility of global caching.

But the only difference across users is a CSS styling, and one that could be controlled in a dynamic way.  So what I did was assign a CSS class to each row named to the user id.  For example:

<tr class="user_2635">...

Then, in the dynamic portion of the generated page I added a style definition targeting the current user:

tr.user_2635 { background-color: #f4f1ac; }

Then the same HTML table can be cached and shared across all users.

I spent a good portion of my life, while I was employed by Digital Chocolate, working on a game called AvaPeeps:FlirtNation.  Initially, it was only targeted at the mobile phone market, but it has just been launched as a beta release as a web game.

The game allows you to design an avatar character and send him/her out on dates with other people's avatars.  You can send your avatar to different locations to hang out and meet other avatars, coach him/her in what to do on dates, and exchange messages with other peeps.

It was a fun and challenging application to work on, and it will be interesting to see how it does with a web audience.  As a member of the team, I spent a lot of time figuring out how to get it to scale up to support millions of users.  Hopefully that work will be put to the test.

It's free, so check it out!