MySQL/PHP - Web Based Game -User specific inventory table or 1 giant table? Another option?

Question

So im making a web based game similar to Torn City, where there could potentially be millions of users. My issue is regarding user inventories. I started out creating dynamic tables based on each user's id. e.g Table name = [UserID]_Inventory. From what Ive found out this can create a load of hacker friendly entries with sql injections and such because of the dynamic creation. My only other option seems to be creating 1 giant table holding every item that every player has and all the varied details of each item. This seems like it would take longer and longer to load once user count increases and the user's inventory will likely be accessed often. Is there another option? My only idea so far is to create some kind of temporary inventory that grabs only the active player inventories. That helps the database search time issues but still brings me back to creating dynamic tables. At this stage I don't really need coding help, rather I need database structure help. Code is appreciated tho. Cheers.

Answer 1

Use the big table. Index it optimally. It should not give you trouble until you get well past a billion rows.

Here's a trick to optimizing use of such a table. Instead of

PRIMARY KEY(id),
INDEX(user_id)

have

PRIMARY KEY(user_id, id),
INDEX(id)

Since the PK is "clustered" with the data and the data is ordered according to the PK, this makes all of one user's data rows sitting next to each other. In huge tables, this cuts back significantly in I/O, hence improves overall speed. Also, it cuts back on pressure on the buffer_pool. (I assume you are using InnoDB?)

The INDEX(id) is sufficient for AUTO_INCREMENT.

There could be more suggestions, but I need more details. Please provide SHOW CREATE TABLE (as it stands now) and the main SELECTs. I am likely to suggest more changes to the indexes, datatypes, and query formulations.

(Dynamic tables is a mistake, and your troubles in that direction have only begun.)