3 ms·

MongoDB really sucks at count. It's a problem I've run into repeatedly with games that automatically submit highscores (ie without a name field/submit button)

MongoDB really sucks at count. It's a problem I've run into repeatedly with games that automatically submit highscores (ie without a name field/submit button) using an API call of ours that saves then returns the rank by checking how many scores are higher or lower than X.... on games that get millions of plays. My solution ultimately ended up being to put the leaderboards that are doing that particular thing into a special "truncate" list that periodically deletes everything after their 50,000th score.

latch 15y ago
I run/built mogade.com, which does the same thing. I blogged about how I switched to Redis for this specific feature (it made it top of HN, weren't you paying attention that day!?). Maybe you'll find it useful, simple test went from over 5minutes to 147ms: http://openmymind.net/2011/5/8/Practical-NoSQL-Solving-a-Real-Problem-w-Mongo-Red/ http://openmymind.net/2011/5/8/Practical-NoSQL-Solving-a-Rea...
- benologist 15y ago
  I distinctly remember The Little MongoDb Book, I must have missed (or not had the problem at that time) the leaderboard one heh.
cheald 15y ago
For things like leaderboards, I'd recommend looking at Redis over Mongo. ZRANK is perfect for this sort of thing. http://redis.io/commands/zrank http://redis.io/commands/zrank
dgottlieb 15y ago
For what it's worth, I had used MySQL on a table with ~20 million rows and doing a count on an index would also be extremely slow. I put in triggers to do counts which worked perfectly for 99% of the sites, but there was some issue (either a bug or programmer error, never figured out which), but a handful of rows on this count table would claim there were a negative number of rows for a given query. (Any row with a negative number was typically zero a count of zero in the real table. In the cases where it was not zero, it was very close to it).

latch 15y ago

I run/built mogade.com, which does the same thing. I blogged about how I switched to Redis for this specific feature (it made it top of HN, weren't you paying attention that day!?). Maybe you'll find it useful, simple test went from over 5minutes to 147ms: http://openmymind.net/2011/5/8/Practical-NoSQL-Solving-a-Real-Problem-w-Mongo-Red/ http://openmymind.net/2011/5/8/Practical-NoSQL-Solving-a-Rea...

benologist 15y ago
I distinctly remember The Little MongoDb Book, I must have missed (or not had the problem at that time) the leaderboard one heh.

cheald 15y ago

For things like leaderboards, I'd recommend looking at Redis over Mongo. ZRANK is perfect for this sort of thing. http://redis.io/commands/zrank http://redis.io/commands/zrank

dgottlieb 15y ago

For what it's worth, I had used MySQL on a table with ~20 million rows and doing a count on an index would also be extremely slow. I put in triggers to do counts which worked perfectly for 99% of the sites, but there was some issue (either a bug or programmer error, never figured out which), but a handful of rows on this count table would claim there were a negative number of rows for a given query. (Any row with a negative number was typically zero a count of zero in the real table. In the cases where it was not zero, it was very close to it).