Inventory Database Design

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 ta开发者_StackOverflow社区ble 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.

---

It may be a good idea to have 3 tables.

1. tblPlayer(idPlayer INT PK NOT NULL AUTO_INCREMENT, Username, Password, etc... )
2. tblItemInventory(idItemInventory INT PK NOT NULL AUTO_INCREMENT, idPlayer FK, idItem FK, ..)
3. tblItem (idItem INT PK NOT NULL AUTO_INCREMENT, Durability, Uses, )

tblItem has up to 24 (idItem=24)

---

There are two lines of thought about setting an index for a table like your inventory. One involves coming up with a surrogate key, basically an auto-incrementing index for the table which has no relationship with your application data. It is only there to serve as an index.

The other way you can enforce an index is to use the combination of playerid and uid. Though if a player can have more than one stack of the same item (like in Diabo or in MMO), you probably can use a combination of playerid and slotid as the index. You can have a composite key consisting of more than one keys in MySQL.