Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have read that table index compression happens just in MyISAM, but I came across an article in dev.mysql that said: InnoDB supports table and index compression! So now what is the pros of MyISAM? Is there any reason left to use MyISAM? (except full-text search)

share|improve this question
1  
Is your question just about table index compression or in what cases would MyISAM be a better choice than InnoDB? –  Craig Efrein Dec 21 '11 at 8:16
    
"full-text search" and "spatial indexes" are probably the only things that should lead someone into using MyISAM engine. –  ypercube Dec 21 '11 at 14:57
    
@CraigEfrein I wonder that we did not have any compression in InnoDB, but now this is another story. Maybe I am not very up to date :-> –  john.locke Dec 21 '11 at 16:17
    
I think that MyISAM is easier when it comes to maintenance and ease of use. I wrote up an answer to explain why, but I wasn't sure if that was what you were looking for. –  Craig Efrein Dec 21 '11 at 17:22
    
I appreciate your answer @CraigEfrein, Let me get this straight: do we have data compression in InnoDB? –  john.locke Dec 21 '11 at 18:11
show 3 more comments

2 Answers

up vote 1 down vote accepted

Recovering Disk Space

As far as recovering InnoDB diskspace goes, you only have to migrate data out of ibdata1 and import it back one time if you enable innodb_file_per_table.

Once you do that, any InnoDB table that gets bloated can be shrunk. For example, if you have an InnoDB called mydb.mytable, you can shrink it with:

ALTER TABLE mydb.mytable ENGINE=InnoDB;

or

OPTIMIZE TABLE mydb.mytable;

Restoring Databases

@Craig is totally right on MyISAM.

As for InnoDB, restoring tables individually can be a nightmare. This is the case because tables are either inside ibdata1 (innodb_file_per_table=0) or outside (innodb_file_per_table=1).

  • For tables outside ibdata1, this is a 24/7 nightmare because the tablespace id of the .ibd file may change if a table is dropped and recreated throughout the course of a business day. If you backup the .ibd with its tablespace id that day, you cannot restore it to it DB Folder by dropping it in. You also cannot do any tricks ALTER TABLE tblname DISCARD TABLESPACE and ALTER TABLE tblname IMPORT TABLESPACE because the tablespace id is hardcoded. You have to write some custom adhoc scripts to pull off such a miracle in conjunction with ALTER TABLE tblname DISCARD TABLESPACE and ALTER TABLE tblname IMPORT TABLESPACE.
  • For tables inside ibdata1, mysqldump is your friend. 'NUFF SAID...
share|improve this answer
add comment

Compression In InnoDB

It exists in the InnoDB version at the table level as of MySQL 5.1. http://dev.mysql.com/doc/innodb-plugin/1.0/en/.

Two advantages on the MyISAM side that I appreciate.

Recovering Disk Space

In MyISAM, you can delete a database on the File System without impacting the other databases and recover disk space. With InnoDB the ibdata file does not shrink when you remove databases. I have only been able to recover space in the ibdata file by deleting it and then restoring each remaining database. Perhaps someone knows a better way?

Restoring Databases

Another advantage to MyISAM is being able to restore a database using a backup system like bacula or Acronis. With MyISAM I can copy a database into and out of MySQL via file system commands, once again without affecting the other databases. With InnoDB, I have to do everything via the a MySQL client.

So I think that MyISAM is simpler to manage in certain situations. In a database that had a mixture of MyISAM and InnoDB, you would probably only be able to restore specific tables, but I haven't tried that yet.

As far as performance goes, a white paper from MySQL I recently read about 5.5 shows that InnoDB is better.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.