Save disk space by compressing MySQL tables

Databases with thousands of records can take up a lot of hard disk space. MySQL has built-in commands and utilities that can compress MyISAM tables.

The popular MySQL open-source RDBMS can handle tables containing hundreds of thousands of records without breaking a sweat. In fact, this scalability is one of the reasons it's becoming so popular with large organizations and their massive databases.

As you might imagine, however, a database with 100,000 records consumes a fair amount of disk space. And if you're a database administrator, you might have wondered if it was possible to somehow optimize MySQL's use of disk space when dealing with such databases.

Well, there is.

MySQL comes with a built-in tool, myisampack, to "pack", or compress, MyISAM tables (MySQL's default table format) so that they use less disk space. The myisampack command works equally well on small and large MyISAM tables, so even if you have a database with a few hundred records, you can use myisampack to compress it and save yourself a little disk space.

Step 1: Evaluate suitability

As with most good things, myisampack comes with one important caveat: tables compressed with it become read-only. This means that you can't use INSERT, UPDATE or DELETESQL commands to change the table, once compressed. To alter data in a compressed table, you will need to first uncompress it, execute SQL commands to change its contents, and then re-compress it.

Because of this, it's important to spend some time thinking about which of your application's tables should be compressed, and which should be left uncompressed. A good rule of thumb here is to only compress those MyISAM tables that contain non-volatile data, or data that won't change during the application life cycle. Examples of such data could be country and city listings, Web site category listings, application configuration defaults.

Step 2: Compress table(s)

Once you've decided which table(s) to compress, check them with myisamchk to ensure that they don't contain any errors:

shell> myisamchk movies.MYI
Checking MyISAM file: movies.MYI
Data records:     146   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links

Note: If you discover errors at this stage, you can fix them by running myisamchk with the -r option.

Assuming no errors, proceed to compress the table(s) with myisampack. You will need to pass myisampack the path to the table's .MYI file, as in the example below:

shell> myisampackmovies.MYI
Compressing movies.MYD: (146 records)
- Calculating statistics
- Compressing file
41.05%
Remember to run myisamchk -rq on compressed tables

Once myisampack has finished compressing the table, it will print the compression percentage for the table.

Step 3: Rebuild table indices

Once the table(s) have been successfully compressed, you need to rebuild the table indices. The myisamchk command can do this too, when invoked with the -rq option.

shell> myisamchk -rqmovies.MYI
- check record delete-chain
- recovering (with sort) MyISAM-table 'movies.MYI'
Data records: 146
- Fixing index 1

You should now verify that the compressed table is free of errors, again with myisamchk:

shell> myisamchk movies.MYI
Checking MyISAM file: movies.MYI
Data records:     146   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links

Step 4: Reload the compressed table(s)

Once the table(s) have been successfully compressed, you need to tell MySQL about them and force the RDBMS to reload the compressed, read-only versions. This is easily accomplished by issuing the FLUSH TABLE command to MySQL using the command-line MySQL client, as follows:

mysql> FLUSH TABLE movies;
Query OK, 0 rows affected (0.05 sec)

You can now verify the table data with a SELECT query, as follows:

mysql> SELECT title FROM movies;
+---------------------+
| title               |
+---------------------+
| Drive Me Crazy      |
| The Insider         |
| The Bachelor        |
| The Bone Collector  |
   ...
+---------------------+
4 rows in set (0.00 sec)

Notice that any attempt to change the compressed table—for example, with a DELETE query—will fail:

mysql> DELETE FROM movies WHERE id=146;
ERROR 1036 (HY000): Table 'movies' is read only

And you're done!

Note: To uncompress a MyISAM table packed with myisampack, invoke myisampack as in Step 2 and pass it the additional --unpack option. Here's an example: