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
