MySQL-MyISAM-Compression

From DevRandom

Using the world database (http://dev.mysql.com/doc/world-setup/en/world-setup.html) we can do this

create table `CityCompressed` like `City`;

insert into `CityCompressed` select * from `City`;

Now the bad part. You have to stop mysql to compress the table.

/etc/init.d/mysqld stop
cd /var/lib/mysql/world
myisampack  CityCompressed.MYI
myisamchk -rq CityCompressed.MYI
/etc/init.d/mysqld start

Now verify the Row_format and Data_length

mysql> show table status like 'City'\G;
*************************** 1. row ***************************
           Name: City
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 4079
 Avg_row_length: 67
    Data_length: 273293
Max_data_length: 18858823439613951
   Index_length: 43008
      Data_free: 0
 Auto_increment: 4080
    Create_time: 2009-06-06 09:54:21
    Update_time: 2009-06-06 09:54:22
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show table status like 'CityCompressed'\G;
*************************** 1. row ***************************
           Name: CityCompressed
         Engine: MyISAM
        Version: 10
     Row_format: Compressed
           Rows: 4079
 Avg_row_length: 19
    Data_length: 79418
Max_data_length: 281474976710655
   Index_length: 30720
      Data_free: 0
 Auto_increment: 4080
    Create_time: 2009-06-26 21:34:36
    Update_time: 2009-06-26 21:34:49
     Check_time: 2009-06-26 21:35:44
      Collation: latin1_swedish_ci
       Checksum: 2011482258
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

Now here's the neat part you can combine the two into Merge Engine

CREATE TABLE `CityNew` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MRG_MyISAM  UNION=(`City`,`CityCompressed`)

Verify the number of rows

mysql> select count(*) from CityNew;
+----------+
| count(*) |
+----------+
|     8158 | 
+----------+
1 row in set (0.00 sec)

Use cases for a setup like this  :)

Save space. You can split data into two sets ; regular(modifiable) and archive(read only)

You essentially have 3 tables to play with and you can run queries against individual ones or the merged on