patternsqlMajor
Does an empty column value occupy same storage space as a filled column value?
Viewed 0 times
samespacecolumnoccupyemptystoragevaluefilleddoes
Problem
I have a table with 2 columns. The type of both columns is set to
In other words, will MySQL reserve storage space for the column (depending on its type) when a row is created?
varchar(38). If I create a row with an empty value for one of the columns, will it take same storage space as if the value was not empty? In other words, will MySQL reserve storage space for the column (depending on its type) when a row is created?
Solution
From the Innodb Physical Row Structure, bulletpoint #7 under REDUNDANT ROW_FORMAT
An SQL NULL value reserves one or two bytes in the record directory. Besides that, an SQL NULL value reserves zero bytes in the data part of the record if stored in a variable length column. In a fixed-length column, it reserves the fixed length of the column in the data part of the record. Reserving the fixed space for NULL values enables an update of the column from NULL to a non-NULL value to be done in place without causing fragmentation of the index page.
From the Innodb Physical Row Structure, bulletpoint #2 under COMPACT ROW_FORMAT
The variable-length part of the record header contains a bit vector for indicating NULL columns. If the number of columns in the index that can be NULL is N, the bit vector occupies CEILING(N/8) bytes. (For example, if there are anywhere from 9 to 15 columns that can be NULL, the bit vector uses two bytes.) Columns that are NULL do not occupy space other than the bit in this vector. The variable-length part of the header also contains the lengths of variable-length columns. Each length takes one or two bytes, depending on the maximum length of the column. If all columns in the index are NOT NULL and have a fixed length, the record header has no variable-length part.
Based on these bulletpoints, here is what a
Now, you must decide between use CHAR and VARCHAR because of what the first point brought out
Reserving the fixed space for NULL values enables an update of the column from NULL to a non-NULL value to be done in place without causing fragmentation of the index page
This will prevent introducing any fragmentation of a row going down the road once non-NULL data is stored. This is something I have discussed before with regards to MyISAM : See my old post What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?.
An SQL NULL value reserves one or two bytes in the record directory. Besides that, an SQL NULL value reserves zero bytes in the data part of the record if stored in a variable length column. In a fixed-length column, it reserves the fixed length of the column in the data part of the record. Reserving the fixed space for NULL values enables an update of the column from NULL to a non-NULL value to be done in place without causing fragmentation of the index page.
From the Innodb Physical Row Structure, bulletpoint #2 under COMPACT ROW_FORMAT
The variable-length part of the record header contains a bit vector for indicating NULL columns. If the number of columns in the index that can be NULL is N, the bit vector occupies CEILING(N/8) bytes. (For example, if there are anywhere from 9 to 15 columns that can be NULL, the bit vector uses two bytes.) Columns that are NULL do not occupy space other than the bit in this vector. The variable-length part of the header also contains the lengths of variable-length columns. Each length takes one or two bytes, depending on the maximum length of the column. If all columns in the index are NOT NULL and have a fixed length, the record header has no variable-length part.
Based on these bulletpoints, here is what a
NULL value takes up for a column's storage- variable length: a NULL value takes up no storage in the row itself
- fixed length: Takes up the reserved space
Now, you must decide between use CHAR and VARCHAR because of what the first point brought out
Reserving the fixed space for NULL values enables an update of the column from NULL to a non-NULL value to be done in place without causing fragmentation of the index page
This will prevent introducing any fragmentation of a row going down the road once non-NULL data is stored. This is something I have discussed before with regards to MyISAM : See my old post What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?.
Context
StackExchange Database Administrators Q#109210, answer score: 22
Revisions (0)
No revisions yet.