Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
Denormalizing followers_no, following_no and last_login makes sense to me -- what does product_no mean in this context? – Jon Seigel Aug 1 '12 at 13:16
Each user will have a shop for him/herself. I want to know how many products each user have. – Alireza Hos Aug 1 '12 at 13:20
AH yes, okay, I read it as "product number" not "number of products." My bad. Yes, that makes sense to denormalize if you'll use it frequently. – Jon Seigel Aug 1 '12 at 13:29
@JonSeigel, will these updates make the user table fragmented? – Alireza Hos Aug 1 '12 at 13:32
I don't know, I'm not a MySQL expert. – Jon Seigel Aug 1 '12 at 13:39
up vote 1 down vote accepted

It most certainly will cause fragmentation, but you must compare it with normalization.


Fragmentation is introduced in a heavy-write environment.

  • DELETEs automatically create empty space by at least the size of the row at the time of deletion.
  • UPDATEs can also cause fragmentation, mostly notably on variable-length data.

Further Links on InnoDB Fragmentation and how to Eliminate it from InnoDB data and system tablespace


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

  • Address Change
  • Spelling Correction
  • Last Name Change (in case a lady get married)

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 InnoDB userinfo table with one of the following



You should split up the user data into user and userinfo tables. A simple INNER JOIN will combine them as needed.

share|improve this answer

I suggest you follow normalization rules. This will minimize the write and update processing (and errors) and simplifies coding of the logic of write and update routines. You can tune reads in many ways. You could use materialized views or indexed views to keep track of counts without queering the records one by one. Don't sacrifice normalization for no obvious reasons.

share|improve this answer
Will updates cause fragmentation on the table? – Alireza Hos Aug 1 '12 at 13:25
I don't know enough about fragmentation to be honest. – NoChance Aug 1 '12 at 14:01

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.