views:

35

answers:

1

I have a table with for columns, a smallint, an integer and two floats. Expected memory for each row is 14bytes (2+4+2*4). I have around 700,000 rows in my table. I've set no primary keys and indices. While the MyIsam type takes around 10MB (average of 15b for each row), the InnoDB type takes more than 30Mb (average of 44b). What am I missing here? Would InnoDB have so much of overhead for each row, or are the table status number not to be trusted. I need to store data which will run into GBs, so need to decide on the storage type by weighing different parameters.

A: 

InnoDB tables do take up more space when compared to MyISAM. Whether the trade-off between storage use and the features offered by InnoDB (crash-safety, transactions, and foreign keys) favours InnoDB is something you will have to decide on. Having myself switched from MyISAM to InnoDB, I wouldn't go back.

It's worth noting that if you are going to go ahead and use InnoDB, you will definitely want to define a primary key (and ideally a short one). This is due to the way InnoDB behaves wrt storage and other indices. See this page.

Hammerite
Ya, this answers my quesion along with this http://stackoverflow.com/questions/2914492/mysql-innodb-table-size-question
Grungean