Page 1 of 1
How many tables should i use
Posted: Wed Nov 02, 2011 11:46 pm
by social_experiment
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
Re: How many tables should i use
Posted: Thu Nov 03, 2011 12:02 am
by Benjamin
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.
Re: How many tables should i use
Posted: Thu Nov 03, 2011 12:28 am
by social_experiment
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?
Re: How many tables should i use
Posted: Thu Nov 03, 2011 4:11 am
by Benjamin
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.