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:


  • 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


  • 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';
    PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

Create a free website or blog at

%d bloggers like this: