Should I split a large mysql table into several?

Question

I have a rather large social network site, which I have been working on for about 2 years (high traffic and 100 files). I have experimented with tuning performance for maximum traffic performance over the past couple of years, and I have learned a lot. Now I have a huge task, I plan to completely reconfigure my social network, so I will redesign mysql DB and all that.

Below is a photo consisting of several mysql tables that I have a question about. Currently, I have a login table, which is used during the login process, as soon as a user visits a site, which you very rarely need to hit the table if you do not edit your email or password. Then I have a user table, which is basically the user settings and profile data for the site. Here I have questions, should better performance split user table into smaller tables? For example, if you look at the user table, you will see several fields that I designated as "setting_", if I just create a separate settings table? I also have fields marked with "count", which can be the total number of comments, photos,friends, emails, etc. So should I create another table to store the total total number of things?

The reason I have them all on 1 table is because I thought maybe it would be better if I could shorten mysql queries instead of hitting 3 tables to get information about each load pages, I could hit 1.

Sorry if this is confusing, and thanks for any advice.

alt text http://img2.pict.com/b0/57/63/2281110/0/800/dbtable.jpg

Answer 1

Should I create a separate settings table?

So should I create another table to store only the total number of things?

There is not a single correct answer for this, it depends on how your application works.

What you can do is measure and extrapolate the results in a dev environment.

, .

, ( ), .

, , , , , , , .

, , , - , , . , 2% .

Answer 2

SELECT * FROM, , 2 100 , . SELECT , , .

Answer 3

:

• ( )

, - , , .

Answer 4

- , , .

Answer 5

, . , . , , , .

Answer 6

, , . , , ... - , , " ". , IP IP- ... .

, , , auth autorization ( ), , " ", , . , DB .

( , , )

Answer 7

, , , :

• MySQL , . , , . , , , , - , - .

• , . , , , , , , , . , , , .

I can’t find a resource on the Internet to justify this next statement, but I remind you that in the talk about MySQL performance, which Jay Pipes gave, he said that the MySQL optimizer has problems after you get more than 8 joins in one query ( MySQL 5.0. *). I'm not sure how accurate this magic number is, but regardless of the fact that joins usually take longer than queries from a single table.