MyISAM Or InnoDB MySQL engine?

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.

4 Comments

Diggy (not verified)
May 13th, 2010 07:01 pm
What would that sed command look like?
May 13th, 2010 07:17 pm

[chia]$ sed -e 's/ENGINE=MyISAM/ENGINE=InnoDb/g' myisam_engine.sql > innodb_engine.sql

This command worked for me

TRouBLeSHooTeR (not verified)
October 30th, 2010 10:13 pm
Thanks, for documentation :)
Ovais Tariq (not verified)
November 16th, 2010 05:36 pm
While you have quite rightly highlighted the limitations of MyISAM and the advantages of Innodb, but I would like to differ on the the "limitation in Innodb" part. If you really do need searching in your application then more often then not I have seen the full text is pretty much limited, I instead advise going the Sphinx or Lucene route. And as far as table compression is concerned, Innodb does support table compression http://dev.mysql.com/doc/innodb-plugin/1.1/en/innodb-compression.html I have seen size of data files go down to about 40% or even less with compression. Innodb has a new file format the “Barracuda” format. Compressed tables and the new row format that stores long columns “off-page” require the use of the “Barracuda” file format. Although MyISAM performs pretty well with the default configuration, Innodb doesn't quite work well with the default configuration, you will have to fine tune Innodb settings like buffer pool size, concurrency. I recommend trying out the newer Innodb plugin instead of the built-in one. You might also want to have a look at http://www.ovaistariq.net/460/why-do-i-recommend-switching-over-from-myi...

Post new comment

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <img> <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <blockquote> <h1> <h2> <h3> <h4> <h5> <h6> <p> <br>
  • You may post code using <code>...</code> (generic) or <?php ... ?> (highlighted PHP) tags.
  • Image links with 'rel="lightbox"' in the <a> tag will appear in a Lightbox when clicked on.

More information about formatting options

Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.