InnoDB's compact row format

[razdaman]

Hi!

For quite some time I’ve used only myisam as storage engine for my mysql tables. During my early years as a developer I though that “myisam is the simplest storage engine, therefore it is also the fastest”. After reading several advanced books about mysql and general database theory I now realize that this isn’t always the truth. Thus I’m now trying to master the features innodb storage engine.

Today I realized that innodb does not used “fixed”/“variable” format-type as I’m used to with MyIsam. Starting with 5.0.3 it uses the “compact” row format as default. This format uses variable row length (right??) as “variable” row format I know from myisam. As far as I have understood, variable row length means less disk space and slower seeks. Therefore myisam uses only “variable” row format when it is “forced to” (if there are any varchat, text columns).

My question is therefore: how come innodb uses a variable row format as default? Isn’t that slower than allocating x bytes for each row so the disk seeks will be faster?

Please enlighten me, I’m a bit confused )

(I hope my English is okay, if not - just ask what I mean, please)

[sterin]

My opinion is that the discussion about pros and cons between static and dynamic row format has grown larger then it actually deserve.

The pro with static row format like speed and simplicity is just about outweighed by the fact that dynamic rows requires less storage.

The reason is that shuffling a lot of data around is expensive and RAM that could be used for caching data is used to store unnecessary empty data structures etc.

That is why most database vendors has chosen a variable storage format.

[razdaman]

Thank you for your reply.

Well, when I switched my user-table (myisam) from variable to fixed length I experienced a considerable performance boost. That’s why I am a bit “afraid” of the “compact” row format.

That being said I can’t say that you are not right - so I’ll guess I’ll just have to try out this row format “in action” because I really want to switch to innodb.

One more question: in order to allow the “fixed” row length, when using myisam, i moved my “profile” (TEXT) column from my users-table to a users_profiles and linked these tables with a user_id.

According to what you are saying, I don’t have to do something like this when using innodb/compact? I mean - I know it’s not ideal - but will the performance drop be as significant with myisam when having TEXT-columns in the user-table?

Thanks…

[sterin]

The comparison is not really correct.

If you have a TEXT/BLOB column in a table and you don’t always select it then breaking it out into a separate table is good practice.

Because in these cases the search time for a table is very much reduced since the table size is much less MB’s.

This alone can speed up a lot of queries quite a bit.

But if your queries use indexes at all times this usually isn’t an issue.

[razdaman]

Ah okay, thank you.

When I used MyISAM I always chose the CHAR datatype over VARCHAR as performance was much more important than disk space.

How do I decide what data type to use when using innodb? - if tailing spaces is truncated in innodb’s compact row format, what is the purpose of using the CHAR data type?

[sterin]

My rule is to always use VARCHAR.

The only exceptions are:
If I am certain that the string I’m going to store is always constant in length or very near the maximum size all the time I can choose CHAR.
For example storing an encrypted password using MD5() the string is always 32 bytes which means it’s a good candidate for CHAR.

If the string is equal to or less than 4 bytes I also choose CHAR.

On a side note it is good to remember the odd behaviour with CHAR that it silently removes trailing spaces from the string. Which means that you sometimes don’t get back what you stored.

[razdaman]

Okay ) Thanks