I was reading the book MySQL high performance, and it mentioned that:
CHAR
is also better thanVARCHAR
for 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?
Best Answer
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:
And then issue:
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:
There would now be a lot of wasted space; I might be interested in reclaiming that space.
This is the fragmentation problem within rows.