In my MMORPG, I recently noticed a problem with inventory items.
At the moment, I store inventory items like this: (very slimmed down version:)
Table: inventoryitems
Primary Key: Integer
ItemID: Integer
CharacterID: Integer
… etc
Then to load an inventory I’ll do a SELECT … FROM inventoryitems WHERE CharacterID=?
Then to save an inventory, I’ll first delete their existing items with DELETE FROM inventoryitems WHERE CharacterID=? and then reinsert their inventory as the server sees it.
The problem with this approach (that I just recently discovered) is that after roughly 4.2 billion item updates (which is easy to hit in an MMO), the numbers overflow and the server starts overwriting old items.
I’ve been thinking about ways to circumvent this problem, and I’ve come up with the following solution:
- Deleted the table inventoryitems
- Added a BLOB column to the Characters table called Inventory, made Inventory’s binary-serializable and I’ll just load and save inventory data into this column for each character.
Now, I get some advantages doing this method:
- I never run out of primary keys.
- I don’t need to do a delete.
I’ve asked a few other people how they would fix this problem, and they said they’d use a 64 bit integer as primary key instead. Now, I don’t believe this is solving the problem, but instead pushing it up another 2^32 levels (roughly 900 trillion).
The question I wanted to ask is, and I’ve done quite a bit of research on this matter but I wanted to get some input from other people, is how do BLOBs perform when compared to multi-row tables like the inventoryitems table?
I’d assume they’d be faster (given my binary-serialization algorithm packs well), but I just wanted to see what other people say.
Thoughts?