Differences between innodb and myisam mysql storage engines

Data in MySQL is stored in files (or memory) using a variety of different techniques. Each of these techniques employ different storage mechanisms, indexing facilities, locking levels and ultimately provide a range of different functions and capabilities. By choosing a different technique you can gain additional speed or functionality benefits that will improve the overall functionality of your application.

Comparison between the MyISAM and InnoDB storage engines of MySQL.

innodb myisam

1

row-level locking table-level locking

2

supports transaction does not support transactions

3

foreign key constraints no foreign key constraints

4

row count is not stored internally and so slow COUNT(*)s row count is stored internally and so fast COUNT(*)s

5

automatic crash recovery no automatic crash recovery, but it does offer repair table functionality

6

stores both data and indexes in one file stores indexes in one file and data in another

7

uses a buffer pool (innodb_buffer_pool_size) to cache both data and indexes uses key buffers (key_buffer) for caching indexes and leaves the data caching management to the operating system

8

ACID(Atomicity, Consistency, Isolation and Durability) compliant not ACID compliant
Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: