MySQL Forums
Forum List  »  Newbie

Re: Estimate table size
Posted by: Rick James
Date: September 07, 2010 11:18PM

Shawn's numbers and math apply pretty well to MyISAM tables, but you have an InnoDB table.

* With InnoDB, data is stored in units of 16KB. 10 rows probably won't come close to filling the first block, so the formula will be quite far off. Instead, fill up the table until there are several blocks worth.

* In InnoDB, the PRIMARY KEY is part of the data, so there is (virtually) no overhead for it.

* In InnoDB, there roughly 29 bytes of overhead per row. Plus, because of the nature of BTrees, you should not count on blocks to be full. 70% full is a ballpark number.

* Each cell in each row takes 1 or 2 bytes extra (again, in InnoDB).

* CHAR(15) should be used only if you expect all the items to be exactly 15 characters long. VARCHAR(15) is usually more appropriate.

* The (15) in INT(15) has nothing to do with the space taken. As Shawn indicates, an INT takes 4 bytes.

* For the sake of completeness (and now for MyISAM), the PRIMARY KEY would take about 6+15 bytes per row, plus 1/70% overhead. About 300MB. (Close to 0 for InnoDB.)

Options: ReplyQuote


Subject
Written By
Posted
September 07, 2010 10:57AM
September 07, 2010 03:01PM
Re: Estimate table size
September 07, 2010 11:18PM
September 08, 2010 07:41AM
September 08, 2010 09:50AM


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.