Table Size 2007-11-02 - By Baron Schwartz
Back
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@(protected)>
> To: Dan Nelson <dnelson@(protected)>
> Cc: Josh <josh2780@(protected)>; mysql@(protected)
> 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/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
