I'm fairly new to using MySQL and I'm trying to understand what the most efficient way to store a player's inventory items in my database is.
So here's the setup:
There's a table called 'player', and each player is assigned a unique 'playerid' which is set as the primary index of the table.
Each player can have up to 24 items in their inventory, and the information on those items is stored in a table called 'player_inventory'. This table has the following fields:
playerid, slotid, uid, stack, uses, durability
'uid' is the id of the item, and 'stack', 'uses' and 'durability' are just values that each item needs (the same type of item in a different slot could have lower 'durability', for example).
The problem being, I can't set an index on 'playerid' in the inventory table because there are up to 24 slot entries per player and none of the other fields are guaranteed to be unique.
So I'm worried when this table has the inventories of 10000 players, there could be potentially 240,000 entries in this table with no index when I go to query it - something tells me that might be very slow?
I have pretty limited knowledge on how to optimize my database, any advice is very welcome.
Indexes -- including a unique index for the primary key -- can be defined over multiple columns.
ALTER TABLE player_inventory ADD PRIMARY KEY (playerid, slotid);
That means the combination of values in those two columns must be unique. But a given playerid may occur on multiple rows, and a given slotid may occur on multiple rows.