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, it has 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 how VARCHAR is subjected to fragmentation in the first place? Thanks in advance.
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.
