char vs varchar in mysql

Question

I was reading the book MySQL high performance, and it mentioned that:

CHAR is also better than VARCHAR 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?

Answer 1

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.