MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
1PLs Company - #1Payday.Loans Agency - Loans online and near me $100-$2500 (Same Day)
Powered by MySQL
 

What are MyISAM formats in MySQL?

MySQL MyISAM storage engine offers different formats but I am not much clear about them. Can you explain MyISAM each format?

Answer No: 166

The MyISAM storage engine is one of more important components of MySQL. MyISAM has many optimizing features. One of other useful features of MyISAM is that it is capable to create table of three formats: static, dynamic, and compressed.  MySQL automatically applies the best type (from two of them) depending on the type of columns you are using. The third, compressed tables, can only be created with the myisampack tool.

MyISAM Static

Static (also known as Fixed-length) is the default format. MySQL automatically uses the static MyISAM variant if the table contains no VARCHAR, BLOB, or TEXT columns.

  • All CHAR, NUMERIC, and DECIMAL columns are space-padded to the column width.
  • Very quick.
  • Easy to cache.
  • Easy to reconstruct after a crash, because records are located in fixed positions.
  • Doesn't have to be reorganised (with myisamchk) unless a huge number of records are deleted and you want to return free disk space to the operating system.
  • Usually requires more disk space than dynamic tables.

MyISAM Dynamic

This format is used if the table contains any VARCHAR, BLOB, or TEXT columns or if the table was created with ROW_FORMAT=dynamic. Although a MyISAM-dynamic table consumes less space than MyISAM-static. If a field’s contents change, then the location will likely need to be moved, causing fragmentation. As the data set becomes increasingly fragmented, data access performance will suffer accordingly. To fix this problem, three remedies are available:

  • Use static data types whenever possible.
  • Use OPTIMIZE TABLE statement or myisamchk to defragment a table statement on a regular basis, which defragments tables and recovers space lost over time due to table updates and deletions.

MyISAM Compressed

This is a read-only type that is generated with the optional myisampack tool. Sometimes you would need to create tables that are intended as read only throughout the lifetime of your application. In this case, you can significantly reduce their size by converting them into MyISAM-compressed tables using the myisampack utility. In this way, giving certain hardware configurations, performance savings could be significant.

Related MySQL FAQs to the Above FAQ

What-are-MyISAM-table-characteristics What are MyISAM table characteristics?

How-to-create-myisam-table-in-MySQL How to create myisam table in MySQL?

How-MySQL-myisam-table-is-stored-on-disk How MySQL myisam table is stored on disk?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2023  www.mysqlfaqs.net
All rights reserved.