InnoDB or myISAM?

22Jun08

That a hard choice. I Also run into some new engines but in their speeds test i realised that none was as fast as the mentioned, wide spread, innoDb or myIsam. In general MyISAM offers speed whereas Innodb offers reliability.

Lets look at both engines in depth.

InnoDB

supports transaction
supports some newer features: Transactions, row-level locking, foreign keys
high volume, high performance
data integrity
maximum performance when processing large data volumes.
CPU efficiency is probably not matched by any other disk-based relational database engine
maintains its own buffer pool for caching data and indexes in main memory
InnoDB tables can be of any size even on operating systems where file size is limited to 2GB

MyIsam

does not supports transaction
increased speed
simplicity
optimization
Resource Usage
has been around longer

Some Test Using these engines

TABLE DEFINITIONS

CREATE TABLE data(
id int not null auto_increment primary key,
name varchar(120) not null,
text varchar(120) not null)ENGINE=InnoDB;

CREATE TABLE dataisam(
id int not null auto_increment primary key,
name varchar(120) not null,
text varchar(120) not null)ENGINE=MyISAM;

ini_set(’max_execution_time’,600);
$connection = mysql_connect(’localhost’,’root’,”) or die(’error in connection!’);
mysql_select_db(’timetest’,$connection) or die(’cannot connect to database!’);
$let = “abcdefghijklmnopqrstuvwxyz”;
$row = 1000;
$sttime = time();

for($i=0;$i<$row;$i++)
{
$s = rand(0,25);
$l = rand(4,10);
$name = substr($let,$s,$l);
$query=mysql_query(”insert into dataisam(id,name,text) values(”,’$name’,’text’)”);
}

$end1 = time();

for($i=0;$i<$row;$i++)
{
$s = rand(0,25);
$l = rand(4,10);
$name = substr($let,$s,$l);
$query=mysql_query(”insert into data(id,name,text) values(”,’$name’,’text’)”);
}
$end2 = time();
$isam = $end1 – $sttime;
$inno = $end2 – $end1;
echo “MyIsam takes:”.$isam.” sec
”;
echo “Innodb takes:”.$inno.” sec”;
?>

Now let see the execution time for these two engine:

Execution 1:
MyIsam takes:1 sec
Innodb takes:33 sec

Execution 2:
MyIsam takes:0 sec
Innodb takes:29 sec

Execution 3:
MyIsam takes:1 sec
Innodb takes:30 sec

here is a comparison between these engines on different hardware installations

Tips

You can use more than one or any combination of these table types in your database.It’s OK to mix table types in the same database! In fact it’s recommended and frequently required.
If you have an issue about a heavy php you can change maximum php execution time. ini_set(’max_execution_time’,600); (600 is the secs)
Or change php.ini
max_execution_time = XXX, max_input_time = XXX, memory_limit = XXX.

Even though MyISAM is faster than InnoDB in the MySQL world, InnoDB is fast compared to any database engine. With InnoDB you get transactions, speed and integrity three features not usually used in the same sentence. My reflex is to always use Innodb unless there is a compelling reason for using MyISAM, and it has to be really, really compelling.

Advertisements


3 Responses to “InnoDB or myISAM?”

  1. 1 cevarief

    Try to use begin and commit transaction statement for innodb between insert to speed up the process :D, and compare again with myisam.

  2. 2 cevarief

    mysql_query(“BEGIN”);
    for($i=0;$i<$row;$i++)
    {
    $s = rand(0,25);
    $l = rand(4,10);
    $name = substr($let,$s,$l);
    $query=mysql_query(”insert into data(id,name,text) values(”,’$name’,’text’)”);
    }
    mysql_query(“COMMIT”);

  3. 3 worthposting

    The bottom line for me is always use innoDB.

    I have a heavy duty script to synchronize 2 databases using mssql and mysql. I ll try to implement it there and i ll be back to share my remarks.

    Thanx anywayz…


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: