best practice question

[Lambneck]

Hi I am new to mysql and am wondering about something. Is better to break up content into as many tables as possible or to contain all content in one table? For example, author in author table, message in message table, date in date table, tags in tags table... etc.

[The Little Guy]

You usually want to beak up the data as much as possible, this usually creates faster queries. You can then join tables in select statements to make a final table.

 

Here are two example tables, the data could be in one but that could slow other things down:

 

members:

member_id

email

password

 

member_info:

member_info_id

member_id

first_name

last_name

display_name

age

 

notice that the two tables both have a column that stores the same value, we will use that to join the two tables together, like so:

 

select * from members m left join member_info mi using(member_id) where member_id = 123 limit 1;

 

Your output then might look something like this:

 

member_id : email : password : member_info_id : first_name : last_name : display_name : age
123 : me@site.com : 324gff8fj48gfj : 1234 : Joe : Awesome : Super_Fly : 15

 

Breaking up data will help speed up queries, because you don't always want all the data from one table, and just because you use column names instead of star won't always speed it up, it still has to look through those columns even if it doesn't use it. When I say look through those columns I mean it has to find the start and end of the column just to ignore it. So if you don't even have those items in the table it can search even faster.

 

The best thing to do is:

http://en.wikipedia.org/wiki/Database_normalization

 

That is the process of making your tables so you will have the least amount of redundant data as possible. This makes for smaller tables and faster queries. Small tables with good indexes can search over 1 billion records in less than 1 second or even less than 1/2 a second or faster. If you want to know where I got those numbers, my company has tables like that, with over 1 billion records, and when I do a query I can search one of them and get results in about 0.02 seconds.

 

I watched a video from Facebook, and they were talking about their table structure, and they use key, value tables meaning that they have only a few columns in each table kinda like a php variable 1 key one value.

 

Another thing I would like to mention, is don't use comma separated values in a MySQL table when you need to search for certain things and it is in a MySQL comma separated list it will be as hard as hell to find anything. As I mentioned above MySQL can hold over 1 billion records and still query amazingly fast, so don't think that because you have a lot of records MySQL will get slower, that usually isn't the case.

 

Hope this helps, I know I wrote more than what you asked for but too much info never hurt anyone right?

[Lambneck]

This was probably the best response I have ever received on a help forum.  Thank you for all the information, it was exactly what I wanted to know. Thank you again.