0

I'll start by explaining my scenario: I am creating a MMORPG and I want to get a better understanding of the most efficient way to store items in an inventory. From my research, I found that most games have an INVENTORY table where the rows have the ITEM_ID, the ITEM_QUANTITY, the USER_ID, and the SLOT_ID.

I'm thinking that this table could potentially have millions of rows.

So If I use a simple statement such as:

SELECT ITEM_ID
FROM INVENTORY
WHERE USER_ID = 25;

then how does the database really "find" the result to return? Does it have to "loop" through every single record until it finds the match? My concern is that, if that is in fact the way it is done, then wouldn't some SELECT FROM WHERE statements take forever?

Thanks, Ian

Ian
  • 11
  • 2
  • Indexes, Ian, Indexes. – Aify Apr 14 '15 at 21:39
  • Read about [indexes](http://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html). – axiac Apr 14 '15 at 21:39
  • Also keep in mind that you don't have to use a relational database. For very large data you end up switching to things like hbase, which handle data retrieval completely differently. – azurefrog Apr 14 '15 at 21:42

1 Answers1

0

You need to follow some basic principles of table creation.

  1. Have an id column
  2. keep the table normalized(check out table normalization online. you will find lots of stuff).
  3. Add indexes on column. For ex: In the inventory table you can have an index on user_id column if you are using it on the where conditions often. The index on user_id groups the table data by user_id and leads to faster query exection.
user207421
  • 305,947
  • 44
  • 307
  • 483
akr
  • 739
  • 4
  • 15