MySQL Row Format: Difference between fixed and dynamic?

Question

MySQL specifies the row format of a table as either fixed or dynamic, depending on the column data types. If a table has a variable-length column data type, such as TEXT or VARCHAR, the row format is dynamic; otherwise, it's fixed.

My question is, what's the difference between the two row formats? Is one more efficient than the other?

Playgoer

Answer 1

The difference really only matters for MyISAM, other storage engines do not care about the difference. EDIT : Many users commented that InnoDB does care: link 1 by steampowered, link 2 by Kaan.

With MyISAM with fixed width rows, there are a few advantages:

1. No row fragmentation: It is possible with variable width rows to get single rows split into multiple sections across the data file. This can increase disk seeks and slow down operations. It is possible to defrag it with OPTIMIZE TABLE, but this isn't always practical.

2. Data file pointer size: In MyISAM, there is a concept of a data file pointer which is used when it needs to reference the data file. For example, this is used in indexes when they refer to where the row actually is present. With fixed width sizes, this pointer is based on the row offset in the file (ie. rows are 1, 2, 3 regardless of their size). With variable width, the pointer is based on the byte offset (ie. rows might be 1, 57, 163). The result is that with large tables, the pointer needs to be larger which then adds potentially a lot more overhead to the table.

3. Easier to fix in the case of corruption. Since every row is the same size, if your MyISAM table gets corrupted it is much easier to repair, so you will only lose data that is actually corrupted. With variable width, in theory it is possible that the variable width pointers get messed up, which can result in hosing data in a bad way.

Now the primary drawback of fixed width is that it wastes more space. For example, you need to use CHAR fields instead of VARCHAR fields, so you end up with extra space taken up.

Normally, you won't have much choice in the format, since it is dictated based on the schema. However, it might be worth if you only have a few varchar's or a single blob/text to try to optimize towards this. For example, consider switching the only varchar into a char, or split the blob into it's own table.

You can read even more about this at:

http://dev.mysql.com/doc/refman/5.0/en/static-format.html

http://dev.mysql.com/doc/refman/5.0/en/dynamic-format.html

Deodar

This page indicates dynamic row format has significance for InnoDB as well. – Juvenile

Another article indicating dynamic row format has significance for InnoDB : Blob Storage in InnoDB - MySQL Performance Blog – Towbin

I tested the insertion of 10 thousand records into a table with `ROW_SIZE = 282B". With InnoDB, it took almost 500 seconds; with MyISAM and MEMORY, it took about 2.97 seconds. – Lait

@Lait - you probably had the setting on that syncs to disk on ever transaction, and you probably had one insert per transaction. InnoDB can run much faster than that. – Cleopatracleopatre

FIXED is removed from MySQL 5.7 for InnoDb – Loggerhead

Answer 2

One key difference occurs when you update a record. If the row format is fixed, there is no change in the length of the record. In contrast, if the row format is dynamic and the new data causes the record to increase in length, a link is used to point to the "overflow" data (i.e. it's called the overflow pointer).

This fragments the table and generally slows things down. There is a command to defragment (OPTIMIZE TABLE), which somewhat mitigates the issue.

Stitch

Answer 3

This page in MySQL's documentation seems to contradict the top answer here, in that DYNAMIC row format means something for InnoDB tables as well:

https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html

Rosebay

Answer 4

Fixed means that every row is exactly the same size. That means that if the 3rd row on a data page needs to be loaded, it will be at exactly PageHeader+2*RowSize, saving some access time.

In order to find the beginning of a dynamic record, the list of record offsets must be consulted, which involves an extra indirection.

In short, yes, there's a slight performance hit for dynamic rows. No, it's not a very big one. If you think it will be a problem, test for it.

Veronica

Is the middle paragraph true? It appears to contradict the accepted answer. (ie: pointer size is merely bigger, not an extra level of indirection) – Cuba

Answer 5

Fixed should be faster and more secure than dynamic, with the drawback of having a fixed char-lenght. You can find this information here: http://dev.mysql.com/doc/refman/5.0/en/static-format.html

Berniecebernier