Multiple users table VS 1 users table? (mysql and php)

Question

I am in dilemma situation. I am not sure if its a good idea to separate the users table. I notice my game highscores table performances, as the numbers growing, the loading is getting slower and slower.

My current users table store all users, which currently about 10k users. I am thinking of splitting the users table (for future) into like this:

Login Table => store user login details

==========================================
= id    | username  | password  | tableid  =
==========================================
=  1    |  user1    |  user1xx  |    1     =
=  2    |  user2    |  user2xx  |    1     =
...
= 20k1  |  user20k1 |  user20k1 |    2     =
etc

Users Data

==========================================
= id    | money  | items  | preferences  =
==========================================
=  1    |  xx    |  xx    |    xx        =
=  2    |  xx    |  xx    |    xx        =
...
= 20k1  |  xx    |  xx    |    xx        =
etc

So, when I try to get users data I just LEFT JOIN query to get the data.

My question is, are there any differences (speed, performances etc) between storing users data in multiple tables and storing users data in single table? (assume indexes and primary key are the same)

My current tables indexes:

Games highscores table => columns: id, gameid, name, score, date

Primary key : id

Indexes: gameid

Login Table => Columns: id, username, password

Primary key: id (userid)

Indexes: username

Users data => Columns: alots

Indexes: id

Answer 1

First, there's no need to break the existing relation into two. You don't want to normalize this time. The split you propose is artificial. DB engine is better than a human with making optimizations if possible.

You can experiment with different storage types, though.

Answer 2

If you're querying for bits of information and you have lots of (edit:)columns, it's actually a really good idea to have them separated and you don't need the tableid field in the users table, all you need is a foreign key in the information table that points to the associated user in the users table.

You can have multiple tables like that and join them as you like, performance will most likely increase.

Answer 3

Sounds like splitting the table won't do you any good. It seems like a 1:1 correlation would occur between the tables, and that would simply add a second query whenever you wanted something from that table.

Try using Partitioning on the table to help with performance in that aspect.

Normalizing is only useful if you have redundant data (so, you have the same user in your user table 5 times). Helpful if you want to lower data usage with particular users' high scores for multiple games, but ultimately it probably won't give you a performance increase on the table.

Answer 4

It sounds that the real question you have here is this: why ma app is slow. First of all splitting data between several tables is not going to help performance. If done right (for reasons other than performance) it will not hurt performance but I doubt it will help.

What's more, in my experience it is a bad idea to optimize based on gut feel. Somehow guesses about what holds your program back are usually wrong. You end up doing a lot of rewriting without any gain in speed.

The first step to speed it up is to find the real bottleneck. You need to add instrumentation and collect some stats to figure out - is it database or app server. Is it a particular sproc or might be the bandwidth of your network. Or may be it is some javascript on your pages.

Only after you know what to fix you can try to fix it.