InnoDB Table Compression – How to Implement in MySQL

innodbmyisamMySQL

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)

Best Answer

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;

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...

Memory Usage

MyISAM

only caches index pages.
shared keycache (sized by key_buffer_size).
You can also set up dedicated keycache, one or more tables per cache table.

InnoDB

caches data pages and index pages.
one buffer pool and one size before MySQL 5.5
1 or more buffer pools starting with MySQL 5.5

Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool.

FULLTEXT Indexes

MyISAM

Supports FULLTEXT indexes

InnoDB

Starting with MySQL 5.6, yes, but still in beta (UPDATE: MySQL 5.6 exists and has FULLTEXT indexes. If you are using FULLTEXT indexing in MySQL 5.6, make sure you are using the InnoDB-specific FULLTEXT options)
Before MySQL 5.6, This means you cannot convert MyISAM to InnoDB.

MySQL 5.5 and back

To locate which MyISAM tables have a FULLTEXT index run this query:

select tbl.table_schema,tbl.table_name from
(
    select table_schema,table_name
    from information_schema.tables
    where engine='MyISAM'
    and table_schema NOT IN ('information_schema','mysql')
) tbl
INNER JOIN
(
    select table_schema,table_name
    from information_schema.statistics
    where index_type='FULLTEXT'
) ndx
USING (table_schema,table_name);

Whatever comes out of this query cannot be converted to InnoDB until you upgrade to MySQL 5.6.

OPTIMIZE TABLE

MyISAM

The MyISAM table is shrunk
ANALYZE TABLE runs index statistics on all indexes

InnoDB

ANALYZE TABLE is totally useless because index stats are always being recollected
With innodb_file_per_table disabled, ibdata1 will grow bigger
With innodb_file_per_table enabled, tablespace (.ibd) file is shrunk

Best Answer

Recovering Disk Space

Restoring Databases

Related Solutions

Mysql – Consistent logical backup of databases that use MyISAM and InnoDB engines

Gotchas When Converting from MyISAM to InnoDB

Memory Usage

MyISAM

InnoDB

FULLTEXT Indexes

MyISAM

InnoDB

MySQL 5.5 and back

OPTIMIZE TABLE

MyISAM

InnoDB

Related Question