List:General Discussion« Previous MessageNext Message »
From:Josh Date:October 27 2007 2:33pm
Subject:Re: Table Size
View as plain text  
Forgot to send the rest of 'show table status'

           Name: UserReports
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 10388
 Avg_row_length: 104
    Data_length: 1081344
Max_data_length: 0
   Index_length: 212992
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-05-19 21:17:58
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL



----- 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