MySQL Forums
Forum List  »  InnoDB

Re: Difference between theoretical and real storage size
Posted by: Rick James
Date: May 18, 2013 01:30PM

Please provide SHOW CREATE TABLE so I won't have to guess.

I guess you are using MyISAM, not InnoDB.
I guess the fields are NULL, not NOT NULL.
I guess you have put 45 characters into each VARCHAR.
I guess you are using CHARACTER SET latin1, not utf8.

Assuming MyISAM...
* 1-8 NULL fields takes an extra byte per row.
* If a row can't be more than 255 bytes long, then there is a 1-byte overhead per row.
* If a VARCHAR field contains N latin1 characters, then it takes N+1 bytes. (utf8 would take up to 3N+1.)
* CHAR(45) would be 45 or 3*45. (No +1; no 'up to'.)
* Indexes are in BTree format, stored separately, and not covered by this computation.
* InnoDB is typically 2-3 times as big as the computation gives you, with a minimum of 16KB.

The first two bullet items are probably the extra 2 bytes; the rest is the beginning of other issues you may face.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Difference between theoretical and real storage size
657
May 18, 2013 01:30PM


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.