Using MyISAM Engine for Custom Tables in Drupal 7

Engine MyISAM

Drupal 7 uses InnoDB as the default MySQL engine for all tables in the database. Innodb is a better option for processing a large volume of data. It gives good support for ACID property, however, there are some cases where the InnoDB falls short of one's expectation. Some common examples include count queries, memory use of Innodb, etc., see here to know more.

But for a Drupal site, it is not necessary for all tables to have a fully transactional and ACID compliant database, especially tables used as a placeholder for items in the queue, and fragile data pulled from Webservice by making API call.  Now if you are aware of this already, you will think MySQL engine can be changed for a table using 'ALTER TABLE tablename ENGINE = MyISAM' or using phpmyadmin UI from the operations tab.

Custom Tables are created in Drupal using hook_schema(). It is possible to add some extra key => value to the schema array in hook_schema() to use MyISAM for our table.

See the below code snippet for the same,

$schema['tablename'] = array(
  'fields' => array(
    //...field definitions
  ),
  'mysql_engine' => 'MyISAM',
);