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
ALTER TABLE userinfo ENGINE=InnoDB;
OPTIMIZE TABLE userinfo;
You should split up the user data into
userinfo tables. A simple INNER JOIN will combine them as needed.