Does an empty column value occupy same storage space as a filled column value?

Question

I have a table with 2 columns. The type of both columns is set to 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?

Answer 1

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 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?.

Answer 2

Regardless of the length you define for your varchar column, the storage space used by an empty column will be the same.

The CHAR and VARCHAR Types

This only addresses the space used by the varchar column and does not consider the total storage space used by the row, its indexes, primary keys and other columns.

As ypercube mentions in his comment, there are additional considerations for the row storage as a whole when at least one nullable column is present.

Innodb Physical Row Structure

The variable-length part of the record header contains a bit vector for indicating NULL columns. If there are anywhere from 9 to 15 columns that can be NULL, the bit vector uses two bytes.)

...

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

And yes, the storage space used changes based on the type you choose, is it fixed or variable, the collation and other factors such as the engine.

MySQL makes recommendations on optimizing data storage here : Optimizing Data Size

Update

One additional consideration with varchar and that's memory. It is important in MySQL to limit the size of a variable length column as much as possible. Even though the column is variable and the storage space used is variable, MySQL will allocate memory in fixed-chunks to store values. For example varchar(200) will use more memory that varchar(5). This is not a storage space issue, but still something to consider when defining your columns.