Convert From MyISAM to InnoDB storage engine

Run this code, you can do it in a code block, as it will only do it once. Be sure to remove code block once the database has been converted.

<?php
$tables = db_query('SHOW TABLE STATUS');
while ($table = db_fetch_array($tables)) {
  if ($table['Engine'] == 'MyISAM') {
    db_query('ALTER TABLE {%s} ENGINE = InnoDB', $table['Name']);
  }
}
?>

Odds are newly created tables will still be MyISAM; you need to change the default storage engine to fix that. Be aware that the size of your database will grow by about 1/4. Also be aware that for large databases, it will take time since it has to copy the data.

Using InnoDB as the default storage engine can cause problems if not configured correctly.

In addition to adding "default-storage-engine = InnoDB" to your my.cnf file, you should also add "innodb_file_per_table". The default behavior of InnoDB is to store all database tables in a single ibdata file. Adding the line "innodb_file_per_table" causes InnoDB to store each new table in it's own file, somewhat similar to the way the MyISAM engine does.

If you leave this line out your ibdata file will grow rapidly and can eventually consume all available space on your disk. I learned this the hard way. The only way to convert existing tables from the single tablespace ibdata set up to separate tablespaces is to dump the entire database with mysqldump, delete the huge ibdata file from disk and restore the database from the dump. The InnoDB engine will then create the tables in individual tablespaces. It still uses the ibdata file for its internal data dictionary and undo logs.

Read Innodb Performance Tuning Tips for additional InnoDB tuning information.

Reference: http://drupal.org/node/51263