Table Size

27/10/2007, 15h09

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/...uirements.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 isthe Data_length value so much larger?

Thanks.

27/10/2007, 15h17

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/...uirements.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

27/10/2007, 15h25

Have you tried optimize table?

On 10/27/07, Josh <josh2780@yahoo.com> 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/...uirements.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?
>
> Thanks.

27/10/2007, 15h09	#1
Josh Messages: n/a Hébergeur:	Table Size 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/...uirements.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 isthe Data_length value so much larger? Thanks.

27/10/2007, 15h17	#2
Baron Schwartz Messages: n/a Hébergeur:	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/...uirements.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

27/10/2007, 15h25	#3
js Messages: n/a Hébergeur:	Re: Table Size Have you tried optimize table? On 10/27/07, Josh <josh2780@yahoo.com> 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/...uirements.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? > > Thanks.

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email