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