I am going to convert all the tables of 500MB database from MyISAM to InnoDB to see whether it will improve the overall performance of a busy Drupal 6 site. I am wondering what is the best (i.e. safest/easiest/fastest) way to do the conversion.

  • This doesn't seems to be a Drupal related question does it ?
    – tostinni
    Jun 7, 2011 at 1:13
  • 2
    Not directly, but it is something that Drupal admins need to do on occasion.
    – mpdonadio
    Jun 7, 2011 at 2:38
  • I updated my answer to use a new SQL command to filter out MyISAM tables that have FULLTEXT indexes. Please rerun all the steps from scratch using my updated answer. Jun 12, 2011 at 2:20
  • If your Drupal site is not configured to search using FULLTEXT indexes, you may want to go to all tables with FULLTEXT indexes and drop those indexes out of those tables. To find all tables having FULLTEXT indexes, run SELECT table_schema,table FROM information_schema.statistics WHERE index_type='FULLTEXT'; Jun 12, 2011 at 2:24

2 Answers 2


As a MySQL DBA, I trust MySQL to do the conversion by having MySQL write the script for me.

Form the Linux command run this query

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' ENGINE=InnoDB;') FROM (SELECT A.db,A.tb,A.tbsize FROM (SELECT table_schema db,table_name tb,(data_length+index_length) tbsize FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A LEFT JOIN (SELECT table_schema db,table_name tb FROM information_schema.statistics WHERE index_type='FULLTEXT') B USING (db,tb) WHERE B.db IS NULL) AA ORDER BY tbsize" > /root/ConvertMyISAM2InnoDB.sql

The script will convert the smallest tables first. This script was also bypass any MyISAM tables that have FULLTEXT indexes.

Ater looking over the script, you can simply run it in MySQL as follows:

mysql -h... -u... -p... -A < /root/ConvertMyISAM2InnoDB.sql

or if you want to see the timing of each conversion, login to mysql and run this:

mysql> source /root/ConvertMyISAM2InnoDB.sql

This should not get messed up because a full table lock happens when the conversion is being executed.

Once all tables are converted you need to tune the MySQL settings for InnoDB usage and scale down the key_buffer.

Please read this for setting the InnoDB Buffer Pool : https://dba.stackexchange.com/questions/1/what-are-the-main-differences-between-innodb-and-myisam/2194#2194

Please read this also : https://drupal.stackexchange.com/questions/1715/what-would-the-optimal-mysql-configuration-for-a-drupal-7-site-be/2367#2367

Give it a Try !!!

  • Roland, I tried your solution but after importing ConvertMyISAM2InnoDB.sql to the database, I get this error: "ERROR 1214 (HY000) at line 585: The used table type doesn't support FULLTEXT indexes". So should I know whether the conversion took place on some tables and how should I resolve this error? Thanks
    – alfish
    Jun 12, 2011 at 1:20
  • I was afraid that would happen. It simply means that one MyISAM table had a FULLTEXT index. Login to mysql and run if as if you want to see the timing. In other words, run source /root/ConvertMyISAM2InnoDB.sql Jun 12, 2011 at 1:33
  • I will try updating the SQL generation script to skip tables that have FULLTEXT indexes. Jun 12, 2011 at 1:36
  • In the meantime, repeat all these steps from scratch. The first line of the regenrated file has the MyISAM file with the FULLTEXT index. Simply delete that first line and rerun the script. Jun 12, 2011 at 1:54
  • Well, by running this cool conversion bash script (yoodey.com/…), I figured out that apparently the only table on my database that uses Full Text is 'search_index'. This cause the conversion to a halt but after canceling the conversion on it, the rest went on smoothly. By running source ConvertMyISAM2InnoDB.sql I could not pinpoint the culprit. Anyway I appreciate your help.
    – alfish
    Jun 12, 2011 at 3:30

I've written a drush command for this a while ago.

 * Implements hook_drush_command().
function convert_drush_command() {
  $items = array();

  // the key in the $items array is the name of the command.
  $items['convert-engine'] = array(
    // a short description of your command
    'description' => "Convert MYSQL Table Type",
  return $items;

function drush_convert_engine() {
  $args = func_get_args();
  $engine = $args[0];

  $result = db_query("SHOW TABLES");
  while ($row = db_fetch_array($result)) {
    $table = array_shift($row);
    drush_log(dt('Converting @table to @engine', array('@table' => $table, '@engine' => $engine)), 'success');
    db_query("ALTER TABLE $table ENGINE = $engine");

Worked for me a year ago or so, not sure if the drush API changed since then.

You can place that in a convert.drush.inc for example in the .drush folder or execute it somehow on your site, for example with the devel execute php block. As a drush script, you can call it like this:

drush convert-engine InnoDB

Warning: If someone does something with the database while these commands run, your database will be messed up completely. Unrecoverably. So, put your site into maintenance mode and make a backup before trying this! And of course, try on a development/testing site first :)

  • 5
    Agree with Berdir, backup your site. While this operation is going on, you database will be LOCKED. If you want a module that can do it, give DB Tuner a shot.
    – mikeytown2
    Jun 6, 2011 at 22:43
  • @mikeytown2: That's a pretty cool module :)
    – Berdir
    Jun 6, 2011 at 22:46
  • 1
    Nice script but it seems quite overkill versus doing this directly un MySQL client.
    – tostinni
    Jun 7, 2011 at 1:13
  • 2
    The script is exactly 5 lines of code. The rest is to integrate it with drush. There is way to do this in a single sql command in MySQL. One way or another, you have to write a script.
    – Berdir
    Jun 7, 2011 at 6:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.