I have a table for users in a social networking site. I want it to be efficient from ground up. If I put mentioned fields on the table then I have to update user's table on each changes. Should I have another table for fields like these that change frequently? Should I be concerned about fragmentation?
Is there any better approach that I'm unaware of?
Best Answer
It most certainly will cause fragmentation, but you must compare it with normalization.
FRAGMENTATION
Fragmentation is introduced in a heavy-write environment.
Further Links on InnoDB Fragmentation and how to Eliminate it from InnoDB data and system tablespace
NORMALIZATION
Any columns you have in a user table that are immutable (i.e., will never experience changes) should act as the main table. Things such as
will produce very little fragmentation since changes of those kinds are rare.
Any information that logs frequent changes to user information should go into a
userinfo
table. This will separate fragmentation issues from the immutable user data. You can easily defrag an InnoDBuserinfo
table with one of the followingCONCLUSION
You should split up the user data into
user
anduserinfo
tables. A simple INNER JOIN will combine them as needed.