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

Improve this question
3
  • Indexes, Ian, Indexes. Commented Apr 14, 2015 at 21:39
  • Read about indexes. Commented Apr 14, 2015 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. Commented Apr 14, 2015 at 21:42

1 Answer 1

Reset to default
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.
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Don't use code formatting for text that isn't code.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.