14.2 MyISAM-Specific Optimizations

Some of the features of MyISAM tables enable you to optimize how you use them. These optimizations include the following:

The MyISAM storage engine supports several different table storage formats that have differing characteristics. You can take advantage of these characteristics by choosing the storage format that best matches how you intend to use a table. For example, if you have a table that you'll only read and never update, you can make it a compressed table. It will take less disk space, and internal index optimizations might make retrievals faster. Section 14.2.1, "MyISAM Storage Formats," provides more details on the properties of these formats.
You can disable index updating when loading data into an empty MyISAM table to speed up the operation. LOAD DATA INFILE does this automatically for nonunique indexes if the table is empty; it disables index updating before loading and enables it again after loading.
Use scheduling modifiers such as LOW_PRIORITY or DELAYED to change how the server assigns query priorities.
Use the OPTIMIZE TABLE and ANALYZE TABLE statements to update internal table index statistics and allow the query optimizer to make better decisions about how to execute queries.
To distribute disk activity, move some of your MyISAM tables to different disks than the one where the data directory is located. Section 14.1.3, "MyISAM Table Symlinking," describes how to use table symlinking.

14.2.1 MyISAM Storage Formats

For MyISAM tables, the table storage format has an impact on query efficiency. The three allowable formats are static, dynamic, and compressed:

Static tables have fixed-length rows, so every row in the table's datafile is stored at a position that is a multiple of the row size. This makes it easier to look up rows, with the result that MySQL typically can process static tables more quickly than dynamic tables. However, static tables on average take more space than dynamic tables. MyISAM can use static table format only if every column has a fixed width. If the table contains VARCHAR, BLOB, or TEXT columns, it cannot be static.
Dynamic tables use a variable amount of storage for each row. Rows are not stored at fixed positions with datafiles. Each row has extra information that indicates how long the row is, and it's also possible for a row to be stored noncontiguously with different pieces in different locations. This makes retrievals more complex, and thus slower. Dynamic tables generally take less space than static tables. However, if a table is updated frequently, this storage format can result in fragmentation and wasted space. It can be useful to run OPTIMIZE TABLE from time to time to defragment the table. Dynamic format is used if the table contains VARCHAR, BLOB, or TEXT columns, or if it is declared with a ROW_FORMAT = DYNAMIC option.
Compressed tables are packed to save space and stored in optimized form that allows quick retrievals. Compressed tables are read-only, so this table format cannot be used for tables that will be updated. To create a compressed table, use the myisampack utility. It can create compressed tables from either static or dynamic MyISAM tables, and can compress columns of any type.

To find out what storage format a table has, use the SHOW TABLE STATUS statement and examine the value of the Row_format field:


mysql> SHOW TABLE STATUS LIKE 'Country'\G

*************************** 1. row ***************************

           Name: Country

           Type: MyISAM

     Row_format: Fixed

           Rows: 239

 Avg_row_length: 261

    Data_length: 62379

Max_data_length: 1120986464255

   Index_length: 5120

      Data_free: 0

 Auto_increment: NULL

    Create_time: 2003-06-06 16:44:16

    Update_time: 2003-06-06 16:44:16

     Check_time: 2003-09-12 17:44:26

 Create_options:

        Comment:

The Row_format value will be Fixed, Dynamic, or Compressed.

14.2.2 Using Compressed MyISAM Tables

A MyISAM table may be converted to compressed form to save storage space. In many cases, compressing a table improves lookup speed as well, because the compression operation optimizes the internal structure of the table to make retrievals faster.

A compressed table is read-only, so a MyISAM table should be compressed only if its content will not change after it has been populated. If you must modify a compressed table, you can uncompress it, modify it, and compress it again. But if you have to do this often, the extra processing tends to negate the benefits of using a compressed table, especially because the table is unavailable for querying during the uncompression and recompression operations.

To compress a MyISAM table, use the myisampack utility. It's also necessary to use myisamchk afterward to update the indexes. The following example demonstrates how to perform this procedure, using the tables in the world database:

Back up the tables, just in case:


shell> mysqldump --opt world > world.sql

Bring down the server so that it won't use the tables while you're packing them.

Change location into the database directory where the world tables are stored, and then use myisampack to compress them:


shell> myisampack Country City CountryLanguage

Compressing Country.MYD: (239 records)

- Calculating statistics

- Compressing file

73.01%

Compressing City.MYD: (4079 records)

- Calculating statistics

- Compressing file

70.94%

Compressing CountryLanguage.MYD: (984 records)

- Calculating statistics

- Compressing file

71.42%

Remember to run myisamchk -rq on compressed tables

myisampack also understands index filenames as arguments:


shell> myisampack *.MYI

This does not affect the way myisampack works. It simply gives you an easier way to name a group of tables, because you can use filename patterns.

As the final line of myisampack output points out, you should run myisamchk to rebuild the indexes. Like myisampack, myisamchk understands index filename arguments for naming tables, so you can rebuild the indexes as follows:
```
shell> myisamchk -rq *.MYI
```
The equivalent long-option command is:
```
shell> myisamchk --recover --quick *.MYI
```
Restart the server.

If you want to assess the effectiveness of the packing operation, use SHOW TABLE STATUS before and after packing the tables. (The server must be running when you use this statement.) The Data_length and Index_length values should be smaller afterward, and the Row_format value should change from Fixed or Dynamic to Compressed. The following examples show the results for the City table.

Before packing:


mysql> SHOW TABLE STATUS FROM world LIKE 'City'\G

*************************** 1. row ***************************

           Name: City

           Type: MyISAM

     Row_format: Fixed

           Rows: 4079

 Avg_row_length: 67

    Data_length: 273293

Max_data_length: 287762808831

   Index_length: 35840

      Data_free: 0

 Auto_increment: 4080

    Create_time: 2002-12-20 17:17:55

    Update_time: 2002-12-20 17:17:56

     Check_time: NULL

 Create_options:

        Comment:

After packing:


mysql> SHOW TABLE STATUS FROM world LIKE 'City'\G

*************************** 1. row ***************************

           Name: City

           Type: MyISAM

     Row_format: Compressed

           Rows: 4079

 Avg_row_length: 19

    Data_length: 79418

Max_data_length: 4294967295

   Index_length: 30720

      Data_free: 0

 Auto_increment: 4080

    Create_time: 2002-12-20 17:17:55

    Update_time: 2002-12-20 17:17:56

     Check_time: 2003-03-17 12:56:53

 Create_options:

        Comment:

To uncompress a compressed table, use myisamchk in the database directory where the table files are located:


shell> myisamchk --unpack table_name

A table must not be in use by other programs (including the server) while you compress or uncompress it. The easiest thing to do is to bring down the server while using myisampack or myisamchk.

If you do not run myisampack or myisamchk in the database directory where the table files are located, you must specify the pathname to the files, using either absolute pathnames or pathnames relative to your current directory.

Another way to uncompress a table is to dump it, drop it, and re-create it. Do this while the server is running. For example, if world.Country is compressed, you can uncompress it with the following commands:


shell> mysqldump --opt world Country > dump.sql

shell> mysql world < dump.sql

The --opt option to mysqldump causes it to include a DROP TABLE statement in the output written to the dump file. When you process the file with mysql, that statement drops the compressed table, and the rest of the dump file re-creates the table in uncompressed form.

< Day Day Up >