MySQL Forums
Forum List  »  InnoDB

Re: Row size
Posted by: Rick James
Date: June 17, 2009 10:33PM

Welcome to InnoDB. In general, the disk space is 2x-3x bigger than for MyISAM.

You example is a rather narrow table; the ratio is even worse.

The math goes something like this:
* 29 bytes overhead for a row
* 1 byte overhead per field (null or not); in some cases it is 2.
* INT is 4 bytes
* 50% overhead on each block.

(29+1+4+1+4+1+4) * 1.5 = about 66 bytes per row average.

74 is not the same as 66 for two reasons...
* the 1.5 is approximate.
* InnoDB does not calculate the number of rows exactly, and the 74 came from
16K * number-of-blocks / approx-num-rows
or 13413384192 / 179204703

One slight compensating thing: The PRIMARY KEY is included in the data for 'free'.

Oh, one more thing: If you don't have an explicit PRIMARY KEY (or other qualifying UNIQUE key), one will be provided for you. It will be an 8-byte BIGINT. Now the estimate is 79.5 bytes. (This is also close enough to 'agree'.)

One more thing -- "int(4)" takes the same space that "int(11)" takes. They are each 4 bytes. Perhaps you wanted SMALLINT (2 bytes).

Options: ReplyQuote


Subject
Views
Written By
Posted
9113
June 16, 2009 06:55PM
Re: Row size
3566
June 17, 2009 10:33PM


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.