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