2

I was reading the book MySQL high performance, it has 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 how VARCHAR is subjected to fragmentation in the first place? Thanks in advance.

|improve this question
  • 2
    I think it means that rows are subject to fragmentation (and fized-length less than vraiable-length rows.) – ypercubeᵀᴹ Dec 27 '13 at 13:57
2

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 fo rthe 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

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.

|improve this answer
  • +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 '13 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... – Shlomi Noach Dec 29 '13 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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