There's a bitmap of which columns in the table allow NULL. This
contains as many bits as there are NULL-able columns in the table,
rounded up to the nearest byte. When a column is NULL, the bit is set to 1.
That said, I'm not sure whether the CHAR's storage space is still
present in the row when the column is NULL. I think it is. Maybe
someone else knows that offhand.
Baron
Josh wrote:
> Thanks for all of your help/information. One additional question... do NULL values
> take up any space?
>
> For example, if I have a column defined as:
> repAccess char(1) default null
>
> When a user should have access to run a particular report, repAccess will be set to
> 'T'. If not, it is left null.
>
> In this example, the rows with 'T' will occupy an additional 1 btye for storing the
> single character, however, for rows with null... does that take up space? I'm only asking
> to give me an idea of what sort of space NULL values take up. One of my tables has
> hundreds of thousands of rows and could potentially have many null values... I'm trying to
> get an idea of whether or not those null values are taking up much space.
>
> Thanks.
>
>
> ----- Original Message ----
> From: Baron Schwartz <baron@stripped>
> To: Dan Nelson <dnelson@stripped>
> Cc: Josh <josh2780@stripped>; mysql@stripped
> Sent: Sunday, October 28, 2007 9:25:11 AM
> Subject: Re: Table Size
>
> Dan Nelson wrote:
>> In the last episode (Oct 27), Baron Schwartz said:
>>> InnoDB has the following extra things, plus some things I might forget:
>>>
>>> a) the primary key B-Tree
>>> b) row versioning information for every row
>>> c) 16k page size; each page might not be completely full
>>>
>>> Those are all counted towards the table size.. Actually, the primary
>>> key B-Tree might not be; I'd need to look that up. But I think it
>>> is. Hmmmm. I just tested -- yes, the PK counts towards table size.
>> In fact, in InnoDB, all indexes count towards table size, since there
>> is a single .ibd file for the whole thing. So you've got the space
>> taken up by your `repid` index to consider as well..
>
> It's true they're in the same file, but the secondary indexes show up in
> the 'Index_length' column in SHOW TABLE STATUS. I was double-checking
> that the primary key contributes to the 'Data_length' column, not the
> 'Index_length' column.
--
Baron Schwartz
Xaprb LLC
http://www.xaprb.com/
