MyISAM vs InnoDB

I'm going to pit two of MySQL's most popular table engines against each other using the four S's as a guide: Speed, Storage, Stability, and Special features.

To test these catagories, I created two identical tables in the same database. One using MyISAM, one using InnoDB.

 CREATE TABLE `test`.`myisam` (
`field1` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`field2` VARCHAR( 32 ) NOT NULL ,
`field3` TEXT NOT NULL ,
PRIMARY KEY ( `field1` )
) ENGINE = MYISAM 

 CREATE TABLE `test`.`innodb` (
`field1` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`field2` VARCHAR( 32 ) NOT NULL ,
`field3` TEXT NOT NULL ,
PRIMARY KEY ( `field1` )
) ENGINE = INNODB 

1. Speed

   I devised a few tests to compare the relative speeds of the two tables. I repeated each query 3 times and took the average execution time for each table.

   Insert 1,000 rows in one query

   MyISAM: An incredible .0094 second average
   InnoDB: A respectable .0849 second average

   SELECT 10,000 Rows

   MyISAM: A fast .0121 second average
   InnoDB: A decent .0302 second average

   Advanced UPDATE Query

   /* 1,000 rows with md5 hashes (21 rows match) */
   UPDATE innodb SET field3 = 'changed'
     WHERE field3 LIKE '%a12%';
   UPDATE myisam SET field3 = 'changed'
     WHERE field3 LIKE '%a12%';
   

   MyISAM: A good .0112 second average
   InnoDB: A slightly worse .0282 second average

   Conclusion

   I left out some measures of speed, but these numbers are pretty clear. MyISAM was anywhere from 2 to 10 times faster than InnoDB when performing the same tasks. MyISAM wins this round.

2. Storage

   Here I'm comparing the disk space the two tables use when storing data.

   Empty Table

   MyISAM: 1kb of disk space
   InnoDB: 32kb of disk space

   1,000 Rows

   MyISAM: 166kb of disk space
   InnoDB: 256kb of disk space

   10,000 Rows

   MyISAM: 1,341kb of disk space
   InnoDB: 2,080kb of disk space

   Conclusion

   MyISAM is again the clear winner. MyISAM appears to use about 60% of the disk space that InnoDB uses to store the same amount of data.

3. Stability

   One measure of stability is the ability of the table to handle simultaneous connections to the database without failing. I haven't tested this myself, but the consensus seems to be that InnoDB is better at this.

   Conclusion

   I personally haven't had stability issues with either table engine, but then again, I haven't had to manage hundreds of simultaneous connections. I'll trust the people that have and declare InnoDB the winner for this round.

4. Special Features

   This is the category that matters most when choosing an engine type. Both MyISAM and InnoDB have very useful features the other one doesn't. I'll take a look at the main unique feature from each table type.

   Transaction Support

   InnoDB is transaction safe. This means that you can roll back changes you make to a database. This is primarily used for rolling back changes if an error occurs.

   For example, let's say you are transferring money between accounts and taking out a fee. You have three queries: take money out, take fee out, and put money in. If the last query fails, you can roll back the first two. This is a must-have for many applications.

   Full Text Support

   MyISAM supports full text indices and full text searches. This lets you search through text fields extremely quickly. Instead of using LIKE on large tables, which is very ineficient, you can use the MATCH AGAINST syntax for a fraction of the time and processing power. This is a must-have if your tables have description fields that are searchable or you store entire articles in a database.

   Using "article_text LIKE '%mysql%'" on 100 5-page articles would take forever. However using "MATCH(article_text) AGAINST('mysql')" would take no time at all. Another advantage is advanced relevancy scores for searches. If an article mentions mysql 3 times, it will be ranked higher. If you search for "the dog and the cat," only the words "dog" and "cat" will be taken into account when calculating relevancy.

   Conclusion

   Which feature is better depends entirely on what your application is.

And the winner is...

InnoDB. Although InnoDB is often slower and uses more space than MyISAM, I consider transactions as too important to give up. If you look at the times, we're talking a difference of at most .05 seconds. Also, most hosting companies today offer at least 100mb of space per database. 10,000 rows with 100 characters each took up 600kb extra using InnoDB. If you store images and files in the database, space might be an issue, but for most applications, it isn't.

There are certain types of applications that should use MyISAM, most notably Search Engines, but for most applications, InnoDB is the right choice.