I'm creating an MMO questing system and one of the quests is to mine ore.
Does this mean every time a user mines ore I need to hit the database and check if that quest is accomplished? It is only one quest and users mine ore often.
Does this seem like overkill? Or is it fine and I just cache the result of that database query... What do you all think?
Database queries are slow and expensive. When you hit the database on every player action in an MMO, then that database will soon become the bottleneck of your whole architecture.
The database in an MMO should only be used for "cold" data. That is to store the data of characters who are currently not in the game. The "hot" data, which is the data of those characters who are currently online, should be copied completely to the gameserver so your game's systems can access and change that data directly withot requiring an expensive network round-trip to the database. When the client disconnects, then that data should be written back into the database and removed from the memory of the gameserver to free up RAM. Additionally, persisting the player-data every couple minutes is also recommended, so players don't lose too much progress in case of a sudden server crash.
If your game is a typical MMO, then "collect X of resource Y" quests will probably be rather common. MMOs tend to have hundreds of those. So it would be useful to find an architecture that scales well no matter how many quests you have. Checking for every single collection quest you have whenever a player picks up an item could become wasteful even if you do it on the gameserver. It might be wiser if each player would maintain a list of currently active collection quests, and check only against that list whenever a player receives an item.
