Let's look at both, shall we ?
To compress a myisam table you have to do the following :
- myisamchk -dvv Table
- myisampack Table.MYI
Remember that after you run the compression command, you also have to re-create any indexes with :
- myisamchk -rq --sort-index --analyze Table.MYI
As you probably have noticed this command also create index statistics and sorts out index blocks. The compression is completely different from the archive engine. In this case you don't have to use an external utility command like 'myisampack' instead you can use directly the SQL command :
- CREATE TABLE .... ENGINE=ARCHIVE;
- ALTER TABLE .... ENGINE=ARCHIVE;
An archive table supports read and insert operations instead myisam compression only read. According to manuals an archive engine is also able to compress a table until 75% of its original size and observing this comparative test that I did on a Test environment I have to admin that this is partially true.
[mysql test] > du -sh *
16K Table_myisam.frm
2.4G Table_myisam.MYD
1.6G Table_myisam.MYI
570M Table_Engine_Archive.ARZ
16K Table_Engine_Archive.frm
16K Table_myisam_Compressed.frm
815M Table_myisam_Compressed.MYD
8.0K Table_myisam_Compressed.MYI
What about read operation ?
As one can see in manuals, an archive engine doesn't support indexes and therefore read access should be slower than myisam read access. As usual I did test between a myisam compressed table and an archive table and these are the results :
mysql> select count(*) from Table_Engine_Archive. where id=5;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (38.17 sec)
mysql> select count(*) from Table_myisam_Compressed where id=5;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (2 min 56.05 sec)
Do you belive it ? The archive engine seems to be faster than myisam table ! By the way, this is only a test, but an archive engine seem to be a handy engine especially when you don't get enough space on a device !
You'll try, won't you ?
See you in my next blog .
Byeeeee
