How to Design Scalable Leaderboards with MySQL and Redis: Interview Tips

Two days ago a reader sent a screenshot of a WeChat step‑count leaderboard, which sparked a common interview question: how to design a ranking system.

Based on a Database

If you haven't seen this before, you might first think of

ORDER BY

in a relational database. The author once built a leaderboard with MySQL for a competition where only ten teams competed, so a simple table with fields like team name, highest score, latest score, and submission time was sufficient. The front‑end polls the API every minute, and a complex SQL query returns the required columns. This works for small data sets, but it won't scale to millions of users.

Based on Redis

The interview often tests knowledge of Redis sorted sets . A sorted set stores members with scores and orders them by score. Members with equal scores are ordered lexicographically. Key example:

sport:ranking:20210227

. Important commands include:

zadd key score member [score member ...] zincrby key increment member zrank / zrevrank key member zrange / zrevrange key start end [withscores]

Adding members:

<code>zadd sport:ranking:20210227 10026 why</code>

Incrementing a member's score:

<code>zincrby sport:ranking:20210227 5000 why</code>

Getting a member's rank (use

zrevrank

for descending order) and score:

<code>zrevrank sport:ranking:20210227 jay</code>

<code>zscore sport:ranking:20210227 jay</code>

To support per‑user friend rankings, include the user identifier in the key, e.g.,

sport:ranking:why:20210227

, so each user sees a personalized leaderboard.

Recent Seven‑Day Leaderboard

Use

zunionstore

(or

zinterstore

for intersection) to aggregate daily sorted sets into a weekly ranking:

<code>zunionstore sport:ranking:why:last_seven_day 7 sport:ranking:why:20210222 ... sport:ranking:why:20210228</code>

Intersection removes members missing on any day, demonstrating how data gaps affect results.

Billion‑User Leaderboard

For massive scales, partition users into buckets (e.g., by rank tier). Store each bucket in a separate Redis sorted set. To get a user's global rank, compute their rank within their bucket and add the sizes of all higher‑rank buckets using

zcard

. Further refinement can add sub‑tiers or convert tier+attributes into a single score for finer granularity.

Beyond the Technical Details

Real‑world leaderboards must consider fairness, avoid the “Matthew effect,” and may require complex scoring formulas (e.g., community influence scores). Simple sorted‑set solutions are a starting point, but production systems often involve additional analytics and weighting.