Hello,

I'm trying to work out the best way to calculate the size of my MySQL database. All my tables are of type InnoDB, and I wish to estimate just how big my datafile should be.

Is the best way to use 'show table status' and multiply the data_length of each table by the max number of rows possible + index_length? I've read that this is not overly accurate.

Is it better to add the number of bytes required for each column * number of rows?

If I have a table with following schema:
CREATE TABLE tbl1 (
cap_id int(2) NOT NULL default '0',
financial_year int(4) NOT NULL default '0',
thu decimal(15,2) NOT NULL default '0.00',
fri decimal(15,2) NOT NULL default '0.00',
sat decimal(15,2) NOT NULL default '0.00',
sun decimal(15,2) NOT NULL default '0.00',
mon decimal(15,2) NOT NULL default '0.00',
tue decimal(15,2) NOT NULL default '0.00',
wed decimal(15,2) NOT NULL default '0.00',
total decimal(15,2) NOT NULL default '0.00',
PRIMARY KEY (cap_id,financial_year)
) TYPE=InnoDB

when I do 'show table status' when the table is empty, I get:
Name: tblBranchWeekly
Type: Innodb
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: NULL
Index_length: 0
Data_free: 0
Auto_increment: NULL
....

If Data_length refers to the size of each row, shouldn't this be:
4+4+(17*8) = 144 bytes? 16384 bytes (16K) seems a little big for a row.

If anyone could help with my understanding of this, I'd be much appreciative

Catriona