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.

Options: ReplyQuote


Subject
Written By
Posted
September 07, 2011 02:03PM
September 08, 2011 08:30PM
September 08, 2011 10:30PM
Re: physical bytes
September 09, 2011 06:27PM
September 11, 2011 12:18PM
September 12, 2011 11:22AM
September 13, 2011 10:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.