vicker313 tech blog

September 1, 2009

Choose Between MyISAM and InnoDB in MySQL

Filed under: MySQL — Tags: , , — vicker313 @ 8:00 am

We can choose how the data store in table at MySQL, by defining the storage engine:

create table test (id int) engine = [myisam|innodb];

It will use the default storage engine stated at database if the engine option in create statement is not defined. Two commonly used storage engines:

MyISAM

  • Fast in select and insert
  • Less reliable
  • No foreign key checking, where user can inset foreign key that does not exist
  • All the files (FRM, MYI and MYD) are placed at database directory (data\databasename), which make the transfer of database is easy
  • Each database data is stored in different data files and directory
  • No need to maintain

InnoDB

  • Slow in select and insert
  • More reliable
  • Has foreign key checking
  • Only FRM files are placed at database directory (data\databasename), while other files like IB_LOGFILE0, IB_LOGFILE1, IBDATA1 and MYSQL-BIN files are placed at data directory, which you need to copy the whole data directory when transferring database
  • All databases data are mixed in a single data file
  • Need to maintain the database from time to time by purging the binary log
    PURGE BINARY LOGS TO 'mysql-bin.010';
    Or
    PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: