MySQL Forums
Forum List  »  Newbie

Re: size of Decimal(18,4)
Posted by: Rick James
Date: January 03, 2011 09:22AM

"Avg_row_length is 16384" -- I specifically used MyISAM for this little test so that I could avoid that misleading number.

InnoDB stores data into blocks of 16KB (16384). It will store some number of rows in a block; that number will vary depending on the properties of BTrees, the insertion order, etc, etc.

Avg_row_length is calculated as the total disk footprint for the data divided by the number of rows in the table.

InnoDB does not maintain an exact count of rows, but estimates the count on demand.

So, for InnoDB, it calculated Avg_row_length as
16384 bytes / (approx 1 row) = 16384
If you add a second row, you will probably get half that.

MyISAM is simpleminded. It simply adds the next row to a plain file. The Data_length (same as .MYD size on disk) grows by exactly the size of the new record. (There are exceptions.) Indexes are handled elsewhere.

Back to your other question...
There are a lot of good arguments for picking InnoDB. Go with it.

You have hit two _minor_ arguments favoring MyISAM:
* The disk footprint is 2x-3x smaller. (This does not really matter until your data is multi-gigabyte.)
* SELECT COUNT(*) is instantaneous in MyISAM, but slow in InnoDB. (For small tables, you won't notice; for big tables, you won't do COUNT(*). So it is no big deal.)

A strong argument in favor of InnoDB (for you)... You are coming from a "transaction"-oriented SQL Server, so you are familiar with BEGIN...COMMIT. InnoDB will be familiar; MyISAM would seem strange (no FOREIGN KEYS, no COMMIT, etc.)



Edited 1 time(s). Last edit at 01/03/2011 09:24AM by Rick James.

Options: ReplyQuote


Subject
Written By
Posted
January 01, 2011 11:16AM
January 02, 2011 05:14PM
January 03, 2011 02:45AM
Re: size of Decimal(18,4)
January 03, 2011 09:22AM
January 03, 2011 10:42AM


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.