Re: physical bytes
Posted by:
Rick James
Date: September 09, 2011 06:27PM
> varchar(l) only restricts max input
Yes, the (1) restricts input. Truncation is (by default) silent. SHOW WARNINGS; may tell you that it happened.
Updating a VARCHAR to have a longer string leads to reconstructing the record and figuring out where to put it.
MyISAM: One of these happens (I don't know which):
* The record is rebuilt with the extra stuff in a different place (and linked from the old start of the record).
* The record is deleted (leaving a hole), and the new, larger, record is stored somewhere else, possibly split. This would imply changing all the indexes, since they have pointers to the row.
InnoDB: Records are stored in 16KB blocks. If there is room in the current block, all is well. If not, then the block is "split" and the BTree is modified accordingly. No index updating is needed (unless the field changed is in an index) -- secondary indexes have the PRIMARY KEY as the way to find the record.
TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT is a string of bytes preceded by a 1/2/3/4 byte length. This is very similar to VARCHAR. BLOB/... also has this structure. TEXT checks the character set; BLOB does not.
If you had a million VARCHAR(10) with 2 characters each, and you UPDATEd all of them to 3 characters each, that would be a lot of churning. But this is a rather abnormal thing to do. Usually only a few rows are UPDATEd; and usually some rows shrink instead of growing.
The MEMORY engine, I think, silently turns VARCHAR into CHAR.
Bottom line: Declare things appropriately, and don't worry about details like this. Obviously I am obsessed with details like this, yet in real life, I rarely do much differently because of it. Also, I rarely can trace inefficiencies back to this level of detail.
If you are building a table with a billion rows, all of this -- and more -- should be carefully considered. 75% of tables have under a million rows.
A related topic: What happens if the table has lots of DELETEs and INSERTs? Again, the MyISAM holes / InnoDB block splits happen. This leads to some inefficiency, but rarely enough to worry about. InnoDB will combine two adjacent blocks that are empty enough, thereby lessening the issue there.