Tuesday, December 4, 2012

Difference between Innodb, MyIsam and Memory engine in MySQL



When you create a MySQL table, you may need to decide which engine to use:  Innodb or MyIsam ?
In a lot of cases, any of them is fine. But there are  difference between Innodb, MyIsam and Memory engine in MySQL.

MyIsam:

default engine of old  MySQL with great performance. if you want to search through large samples of text efficiently, you want a FULLTEXT index - this allows people to search things like webpages in a page-ranked "google-style", and it's been really helpful. Only MyISAM can do this. MyISAM is faster than InnoDB. The only advantage InnoDB has over MyISAM is that it supports row locking, while MyISAM only supports table locking.

InnoDB:

support transactions, row-level locking and foreign keys. If you want to enforce referential integrity between tables, you must use Innodb.
Starting from MySQL 5.5.5 (or early?), the default storage engine for new tables is InnoDB.
According to MySQL document:
http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html
 "In 2010, hard drive and memory capacity and the performance/price ratio have all gone through the roof. Users pushing the performance boundaries of MySQL care a lot about reliability and crash recovery. MySQL databases are big, busy, robust, distributed, and important.
InnoDB hits the sweet spot of these top user priorities. The trend of storage engine usage has shifted in favor of the more efficient InnoDB. With MySQL 5.5, the time is right to make InnoDB the default storage engine."

Memory:

Hased based, store in meomery, useful for temporary tables.

To change default engine:
mysqld --default-storage-engine=InnoDB
or modify MySQL configuration of my.cnf 

To find which engine is default using mysql command line:
  SHOW ENGINES;

To check the MySQL version:
SHOW VARIABLES LIKE "%version%";

Example code of creating edu_news table using MyISAM engine
DROP TABLE IF EXISTS `cnsh_lms`.`edu_news`;
CREATE TABLE  `cnsh_lms`.`edu_news` (
  `news_id` mediumint(8) unsigned NOT NULL auto_increment,
  `course_id` mediumint(8) unsigned NOT NULL default '0',
  `member_id` mediumint(8) unsigned NOT NULL default '0',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `formatting` tinyint(4) NOT NULL default '0',
  `title` varchar(100) NOT NULL default '',
  `body` text NOT NULL,
  PRIMARY KEY  (`news_id`)
) ENGINE=MyISAM AUTO_INCREMENT=45 DEFAULT CHARSET=utf8;

No comments:

Post a Comment