MySQL Forums
Forum List  »  Newbie

Re: Estimating disk space required for MySQL DB
Posted by: Rick James
Date: June 14, 2009 01:19PM

Per row:

INT is 4 bytes. (MEDIUMINT is 3, etc)
VARCHAR/VARBINARY(nnn) is 1 or bytes + the length of the string (guestimate an average); 1 if nnn<=255
CHAR/BINARY(nnn): nnn bytes
TEXT/BLOB: 2 + length; (TINYTEXT is 1+length, etc)
DATETIME: 8 bytes
DATE: 3 bytes
TIMESTAMP: 4 bytes
FLOAT: 4 bytes; DOUBLE: 8 bytes

NULL: add 1 _bit_ for the null flag, and subtract off the length above, as needed.
UNSIGNED, AUTO_INCREMENT: no impact

The calculations above work pretty well for MyISAM tables (before considering indexes). Multiply it by 2 to 3 for InnoDB (hard to be more precise).

Indexes: This depends a lot on the engine type...

MyISAM indexes (including PRIMARY KEY): For each index, independently calculate the size (above) for the field(s) in the index, then add 4-6 bytes (4 for older versions, 6 for newer; MAX_ROWS can change that to 2-7). Multiply the result by 1.5.

MyISAM FULLTEXT index: Lots of space -- some multiplier on top of the TEXT size.

InnoDB PRIMARY KEY: No extra space; it is included in the data size.

InnoDB Secondary key: For each one... Calculate the size of the field(s) in both the index plus and the PRIMARY KEY. Again, inflate by 2x-3x. Going through this arithmetic will make it clear that a big PRIMARY KEY plus lots of secondary keys adds up to a lot of disk space.

Once you have the table built, you can see the actual size via
SHOW TABLE STATUS LIKE 'tablename'\G

Also, for MyISAM, the .MYD and .MYI files are the data and index files.

Sounds like your database will be under a gigabyte -- not very big in current terms.

Options: ReplyQuote


Subject
Written By
Posted
Re: Estimating disk space required for MySQL DB
June 14, 2009 01:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.