Jan 19 2008

MySQL Database Storage Engine: InnoDB vs. MyISAM

Over the last few months, I have really enjoyed working with the InnoDB storage engine in MySQL because of its support for Foreign Key Constraints. If you try and compare InnoDB to MyISAM, there is really only one choice, InnoDB. MyISAM was the former default storage engine until InnoDB replaced it. There reason that InnoDB is becoming so popular is because it allows for transactions which become very helpful when changing a lot of similar data over several tables.

For example:
If you have a table that holds a bunch of event types and you need to update the primary key to a record to add 1000 (for whatever reason). With MyISAM, you would need to go into every other table that has that ID and change them as well. With InnoDB, you assign it a foreign key to the other tables and as soon as you update the first table, it will automatically update all the other tables linking to it. This will save on the number of queries that you have to do and saving valuable time.

The other part that I enjoy it is because it allows for proper data disposal. If you have a user who goes to upload 2400 pictures over a years time and then decides that they want to delete their account. Not only have they put hundreds of large files on your server, now you have to go through each possible place that the user could have added information. With InnoDB and you are not wasting space on your server for users who are no longer going to come to your site.

However, linking your data is not the most important reason to use InnoDB, the best reason is the performance difference. I was reading an article by the MySQL Performance Blog which compared MyISAM, InnoDB and Falcon. First of all, I have never heard of Falcon, so I can’t compare that, but there is a noticeable difference in performance. InnoDB has much better results when running more queries while increasing the number of threads. These results will vary from server to server, but if InnoDB is setup right, it will help your database structure more when updating a lot of linked tables.

About the author

Timothy Haroutunian

Timothy Haroutunian is a ServiceNow Cloud Implementation Specialist at Acorio. ServiceNow is an IT Management solution that allows for a complete view of your IT and physical environment.

Permanent link to this article: http://www.armenianeagle.com/2008/01/19/mysql-database-storage-engine-innodb-vs-myisam/


1 ping

  1. Pages tagged "engine"

    […] bookmarks tagged engine MySQL Database Storage Engine: InnoDB vs. MyISAM saved by 1 others     KURTCOBIANLOVER bookmarked on 01/20/08 | […]

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.