I'm currently working on an MMO and need help designing a database for logging events during games. When an event occurs, it happens in a particular region of a particular map at a particular time, by a particular player.
We've been pretty concerned about DB size and performance, since there could be up to a couple million players, millions of games, and thousands of events per game.
We've decided to make things easier by grouping the stats together into chunks of time and space, that way we can reduce size and still get some sort of use out of the data.
So far we have these fields for our Events table:
MapID - which map
MapRegion - which linear section of the map
TimeRegion - which moment in time
EventType - what event occurred
Count - how many times this happened
However, performance for updating the Count is terrible. For ~200 random events on a map 20 map regions wide with 10 kinds of events in one time region, updating the database took 10.396 seconds. This is with optimal grouping, meaning that event count increments are grouped as much as possible for update cycles.
The worst part of all is that we'd like to have maybe 150 map regions wide with 20-30 kinds of events.
Is there a better way to do this? Should we not group things like this and capture more raw data instead? Is a DB even the right thing to use in this case?