MyISAM Or InnoDB MySQL engine?

Written on May 11th, 2010 at 17:00 pm by chia

• InnoDB
• MyISAM
• Mysql
• Sysadmin

Most people don't bother about what MySQL engine they are using. Generally, it doesn't even matter, the default MyISAM does the job really well but sometimes switching to some other engine or at least knowing what others are capable of, could speed up a lot of things for you. Managing transactions for a Financial website was how I came across InnoDB engine at first. In this article, we will see the difference between MyISAM and InnoDB MySQL engines.
So, lets begin by taking a look at all the engines. Type the following command in the mysql prompt.

mysql> SHOW ENGINES\G;

You will see something the following engines, with a whole bunch of other information.

InnoDB   
MRG_MYISAM
BLACKHOLE 
CSV       
MEMORY
FEDERATED
ARCHIVE  
MyISAM    

As you can see they are a lot but we will focus on MyISAM and InnoDB here. Lets start with their limitations:-
MyISAM limitations

• No Foriegn keys and cascading deletes and updates
• No rollback abilities
• No transactional integrity (ACID compliance)
• Row limit of 4,284,867,296 rows
• Maximum of 64 indexes per row

InnoDB Limitations

• No full text indexing
• Cannot be compressed for fast, read-only

MyISAM uses table level locking and this can be a great problem if your database INSERT/DELETE/UPDATE load is very high. If this is the problem then you should try converting it to INNODB. It manages non transactional tables. It has fast storage and retrieval, as well as full text searching capabilities. Its supported by every MySQL and comes as a default engine.
When to use MyISAM?
MyISAM is designed with the idea that your database is queried far more than its updated and as a result it performs very fast read operations. If your read to write(insert|update) ratio is less than 15% its better to use MyISAM.
When to use InnoDB?
InnoDB uses row level locking, has commit, rollback, and crash-recovery capabilities to protect user data. It supports transaction and fault tolerance.
Convert from one type of engine to other.
You can create a table and tell which storage engine to use, if none is specified MyISAM is used

CREATE TABLE table_name (rid INT) ENGINE = INNODB;
CREATE TABLE table_name (rid INT) TYPE = INNODB;

TYPE is older term and is supported for backward compatibility.
You can set the default storage engine to be used during the current session by setting the storage engine variable:

SET storage_engine=INNODB

Converting tables from one storage mechanism to another can be done by using the alter command:

ALTER TABLE table_name ENGINE = INNODB

Ok, it could be difficult if you have many tables to manually run alter query. So here's another way that will work.
DUMP the sql into a file using mysqldump.
Using sed command update to INNODB engine from MYISAM in the sql file.