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. It only takes a minute to sign up.
Sign up to join this community
I was reading the book MySQL high performance, and it mentioned that:
CHARis also better thanVARCHARfor data that’s changed frequently, because a fixed-length row is not prone to fragmentation.
But the thing I could not understand is how VARCHAR is subjected to fragmentation in the first place?
To elaborate on ypercube's correct comment, the problem is not that there is fragmentation on the VARCHAR, but in rows that contain variable length data.
If I were to have a table with these values:
+---+-------+
| i | v |
+---+-------+
| 1 | some |
| 2 | small |
| 3 | text |
+---+-------+
And then issue:
UPDATE my_table SET v = 'dinosaur' WHERE i = 2
Then, depending on the storage engine, I might find that there's just no room for the word 'dinosaur' within the existing row location, since rows were written sequentially and compactly to disk. That might mean I would need to change the location of row #2, or place my 'dinosaur' text on an external location, or what have you.
On the other hand, if I were to issue:
UPDATE my_table SET v = 'a'
There would now be a lot of wasted space; I might be interested in reclaiming that space.
This is the fragmentation problem within rows.
