Convert MySQL tables to UTF8 InnoDB

By default, Drupal 6 database tables are installed as MyISAM (Drupal 7 switched to InnoDB). It appears that specific modules already have InnoDB schema's implemented. This results into a mixed-mode database.

What is the difference?

Well, MyISAM has faster table reads and uses less memory. But InnoDB is better for anything else, like: data integrity, reliability and recovery, concurrency (row-level locking), better indexing (which means better table scans and data searches). The Drupal Pressflow distribution has optimized most core queries for InnoDB already. You can convert your MyISAM tables to InnoDB manually, or using the DB Tuner module. Here's a manual SQL script:

Single table: ALTER TABLE tablename ENGINE = InnoDB;

All tables (exactly like this): SELECT CONCAT(CONCAT('ALTER TABLE `',table_name,'`'),' ENGINE=INNODB;') FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE';

Advantages

  • Better performance and reliability for active, dynamic sites

Disadvantages

  • Slower performance for big, unchanging, static sites

References