List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:October 27 2007 2:30pm
Subject:Re: Table Size
View as plain text  
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.

Josh wrote:
> mysql> show create table UserReports\G
> *************************** 1. row ***************************
>        Table: UserReports
> Create Table: CREATE TABLE `UserReports` (
>   `rolID` int(10) unsigned NOT NULL,
>   `repID` int(10) unsigned NOT NULL,
>   PRIMARY KEY  (`rolID`,`repID`),
>   KEY `repID` (`repID`),
>   CONSTRAINT `UserReports_ibfk_1` FOREIGN KEY (`rolID`) REFERENCES `UserRoles`
> (`rolID`) ON DELETE CASCADE,
>   CONSTRAINT `UserReports_ibfk_2` FOREIGN KEY (`repID`) REFERENCES `Reports`
> (`repID`) ON DELETE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
> 
> 
> 
> ----- Original Message ----
> From: Baron Schwartz <baron@stripped>
> To: Josh <josh2780@stripped>
> Cc: mysql@stripped
> Sent: Saturday, October 27, 2007 10:17:32 AM
> Subject: Re: Table Size
> 
> Josh wrote:
>> Hello,
>>
>> I have a database that is growing at a rate of 4-5 MB per day (that number is
> getting larger as well).  Not too bad but I'm trying to clean up the tables to minimize
> the amount of space they take up.
>>
>> I have one particular table that has 2 columns:
>> rolID  int(10) unsigned
>> repID  int(10) unsigned
>>
>> Both are foreign keys to other tables.
>>
>> A "show table status" shows that this table has a Data_length=1081344 bytes.
>> http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that
> integers take up 4 bytes of space.
>> There are currently 10472 rows in the table.
>>
>> 10472 x 4 x 2 = 83776
>>
>> What am I calculating wrong?  Why is the Data_length value so much larger?
> 
> It depends on the storage engine, indexes, and possibly other things. 
> What's the output of SHOW CREATE TABLE for this table?  If you can't 
> tell us that, what's the storage engine?
> 
> (yes, I know Index_length is separate, but humor me).
> 
> Baron

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