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 <[EMAIL PROTECTED]>
To: Josh <[EMAIL PROTECTED]>
Cc: mysql@lists.mysql.com
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
