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?

  • 3
    I think it means that rows are subject to fragmentation (and fized-length less than vraiable-length rows.) Dec 27, 2013 at 13:57

1 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.

  • +1 Fragmentation is bad because it needs extra disk seeks to fetch the whole row otherwise it would have been a single disk seek (if the whole row is fitted to the disk page). Am i correct?
    – Trying
    Dec 29, 2013 at 9:44
  • Yes, but that's just the small part. Fragmentation may also lead to rows which are "natural neighbours" reside in distant locations on disk, which means more I/O on queries that may run faster. Fragmentation also wastes your disk space... Dec 29, 2013 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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