MYSQL - table size seems not normal

Question

I have a table with a schema like this :

id int(10) PK,
fk_id int(10) FK,
c3 int(10),
c4 timestamp,
c5 tinyint(3),
c6 varchar(50) latin1_swedish_ci,
c7 tinyint(3),
c8 tinyint(3),
c9 tinyint(1)

Now this table has 10,000 records and its size is 1.8 MB which the size of indexes is 0.3 MB and size of data is 1.5 MB according to what MYSQL says.

But all records for the filed c6 are almost 10 or 15 character length strings, and if you calculate the maximum incoming size it would be like this:

(4+4+4+4+1+15+1+1+1)*10000 = 350 KB

But the data size is 1500 KB. Now I'm confused why the size of this table is growing with a high rate and don't know the reason of this difference. Is it possible that MYSQL is showing wrong data size? or maybe wrong combination of index size and data size? or any other reason?

I'm really confused , thanks for your help.

Answer 1

First thing I would suggest would be to optimize the table

OPTIMIZE TABLE mytable  

I would expect there to be some overhead for InnoDB, see here:

http://dev.mysql.com/doc/refman/5.5/en/innodb-physical-record.html

but not as much as you've indicated. Could be that there is fragmentation and unused space, especially if you do frequent inserts and updates to that table. Optimizing the table should reclaim that space.