Query optimization using primary key

I have a database with two tables: one that keeps information about each user and one that keeps information about the games each user is currently playing. Each user row and each game row has a unique primary ID so that whenever I want to fetch a particular row, I can obtain it by querying with the unique ID (which to my understanding is faster since the row can be fetched through indexing instead of going through each row in the table)

Now, each entry in the game table contains information about the user, so if I wanted to fetch every game for a particular 开发者_如何学运维user I would be able to. However, this is how I am currently fetching each game for a user:

I save every unique ID for each game for each user delimited by ':'. I then split this list and do a separate query for EACH game ID. So my question is, is it more efficient to do a separate query using the primary ID for each individual game, or is it better just to do a "SELECT * FROM games WHERE userID='theUsersID'".

To put it in a more general form, is it better in the long run to do several queries against several unique IDs, or doing one query but having to look through every entry in the database? Just to give an idea of my database load, I typically have 10,000 users, each with about 10 games at once. So the comparison is 100,000 queries using a primary key for each query, or 10,000 queries but having to go through all rows for each query.

Thanks.

MySQL won't even skip a beat with the low volume you are considering. Allow future flexibility. Your "games per user" table should be UniqueID, UserID, GameID --- plus anything else you might want to track on a per user, per game combination... which is most frequent, last time played for a particular game, etc. I would NEVER suggest concatenation of unique games by any delimiter. This way, you can easily query...

Who likes a particular game by querying on the game ID, or how many distinct games does the average person like to play... What are the top common games among users.

Simple to have multiple indexes on the table.. One on the Primary ID (required), one by User ID and Game ID (for optimized search for game by user first), another by Game ID and User ID (for searching for particular games)

When you do "SELECT * FROM games WHERE userID='theUsersID'", the database engine shouldn't have to actually lookup ALL of the database entries.

If this is a common query, then the userID column should have an index, so that the restriction on the where can be processed quickly. In the long run, it'll be much more efficient to do only one query then one for each game.

So to the best of my understanding, there is a many to many relation between Users and Games.

Each User play many games Each Game has many users.

and you are currently storing the games played by each user in the users table

User ID | Games ID's
1 | 45:23:12
2 | 23:66:11

So your tables are not normalized. Not even 1NF. Consider Normalizing your Data.

One table for Games, which you already have. One table for users, you have this too. One Glue Table, users_games, with :

ID | userID | GameID
1 | 1 | 45
2 | 1 | 23
3 | 1 | 12
4 | 2 | 23
5 | 2 | 66
6 | 2 | 11

So for retrieving Data about one user, you will use Joins

Select GameID from users u join users_games ug on u.id=ug.userID where u.id='2';

and you can extend columns in the users_games table.

This model will be scalable and more manageable.

Hope that makes sense :)

SELECT * FROM games WHERE userID=userID would be the best model;

if you nedd to connect multiple games to one user use LEFT JOIN, so you will be able to do it with one query

in the games table have foloving columns id | userID | on id put Unique AI and userID can be simple index

As far as i understand, your tables are built like this:

Table_User:
UserId (PK);
UserInformation;
UserInformation2; 
...

Table_Games:
GameId (PK);
UserId (PK);
GameInformation;
...

So, doing a LEFT-Join:

SELECT * 
FROM Table_Games G 
LEFT JOIN Table_User U on (U.UserId = G.UserId) 
WHERE UserId = 'MyUserId'

might help you.

Here, you can also SELECT the User by an unique Name or sth. else based on Table_User!

Regards!

Performing one big query instead of many small ones is usually preferable because:

• It can be performed in just one database round-trip instead of many. This is especially important when database round-trips need to go over the network.
• Allows database engine to use more advanced query plan than simple NESTED LOOPS that you are emulating in your code ("big" databases such as Oracle are also capable of doing MERGE or HASH JOINs, not sure about MySQL).

Doing a JOIN on your two tables should do the trick.