Im working on an application that will require a bit of user information. My thinking about this is as follows: Have 3 seperate tables linked by a foreign key (user_id). What is the better option here, using a single table containing all information about a user or splitting the larger table into 3 smaller tables. The site will probably have alot of tracffic so i'm thinking this (3 tables) will be a better option if lots of people access the database simultaneously.
--------
user_tbl
--------
id
username
password
salt
user_id
session_id
--------
user_info_tbl
--------
id
user_id
real_name
surname
likes
dislikes
gender
---------
user_activity_tbl
---------
id
data
activity
user_id
How many tables should i use
Moderator: General Moderators
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
How many tables should i use
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Re: How many tables should i use
I don't see any reason to split those into 3, unless there's a couple fields that are large and aren't used often. In that case just those two could go into another table just to reduce any potential disk io.
Splitting the tables like that would likely HURT performance and increase production expenses. I've seen lots of large sites (>50M page views per hour) with many more fields than that in user tables.
Splitting the tables like that would likely HURT performance and increase production expenses. I've seen lots of large sites (>50M page views per hour) with many more fields than that in user tables.
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: How many tables should i use
Thank you for the feedback; At the moment i'm not sure about the size of specific fields but in terms of usage of each field, the type of site would probably use all the fields most of the time. The given amount of fields are only an estimation and will definitely increase. Any idea what amount of fields would be 'too much' for a single table?
You mention large fields, do those include fields of type TEXT?
You mention large fields, do those include fields of type TEXT?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Re: How many tables should i use
Well by large I mean BLOB and the like. Text would be an example. The key to remember is that MySQL uses indexes to find records. Each query can only use 1 index per table. This means you cannot both query by (WHERE location = 'foo') and sort (ORDER BY age DESC) using different indexes. In other words, you might have location and age indexed individually, but MySQL will only use one in a single query. As long as your queries are able to hit an index in the WHERE clause you should be good to go. As the site grows you may need to tweak memory allocations in the my.cnf file.
There will be times when a table scan may be necessary. It is this case that separating the very large fields may help. That's something I have never tested, I'm only going with my intuition on that.
There will be times when a table scan may be necessary. It is this case that separating the very large fields may help. That's something I have never tested, I'm only going with my intuition on that.