List:General Discussion« Previous MessageNext Message »
From:Josh Date:November 2 2007 2:56pm
Subject:Re: Table Size
View as plain text  
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.
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