MySQL Row Format: Difference between fixed and dynamic?

by

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

More Related Contents:

  1. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0
  2. Mysql query to retrive data from join of three table [duplicate]
  3. How to reduce the size of google spreadsheet using SQL?
  4. How To have Dynamic SQL in MySQL Stored Procedure
  5. Can MySQL split a column?
  6. mysql Foreign key constraint is incorrectly formed error
  7. Unknown Column In Where Clause
  8. Left Join not returning all rows
  9. MySQL match() against() – order by relevance and column?
  10. Maximum length for MySQL type text
  11. How to count words in MySQL / regular expression replacer?
  12. gem install: Failed to build gem native extension (can’t find header files)
  13. Entity-Attribute-Value Table Design
  14. Mysql localhost != 127.0.0.1?
  15. Filter by COUNT(*)?
  16. MySQL concatenation operator
  17. SQL Group By with an Order By
  18. How to Convert UTC Date To Local time Zone in MySql Select Query
  19. MySQL said: Documentation #1045 – Access denied for user ‘root’@’localhost’ (using password: NO)
  20. MySql – Way to update portion of a string?
  21. Using reserved words in column names
  22. MySQL INNER JOIN select only one row from second table
  23. How to select only date from a DATETIME field in MySQL?
  24. UPDATE all records with a random number from a range
  25. mysql sorting of version numbers
  26. Get Insert Statement for existing row in MySQL
  27. DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled
  28. Can’t access mysql from command line on a Mac
  29. Insert data into table with result from another select query
  30. MySQL LIKE vs LOCATE

Leave a Comment