List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:November 2 2007 3:14pm
Subject:Re: Table Size
View as plain text  
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/
Thread
Table SizeJosh27 Oct
  • Re: Table SizeBaron Schwartz27 Oct
  • Re: Table Sizejs27 Oct
Re: Table SizeJosh27 Oct
  • Re: Table SizeBaron Schwartz27 Oct
    • Re: Table SizeDan Nelson28 Oct
      • Re: Table SizeBaron Schwartz28 Oct
Re: Table SizeJosh27 Oct
Re: Table SizeJosh2 Nov
  • Re: Table SizeBaron Schwartz2 Nov