InnoDB or myISAM?

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.