Using MyISAM engine for custom tables in Drupal 7

January 24, 2014 | 3 Comments | Submitted by Ganesan

Drupal 7 uses InnoDB as default MySQL engine for all tables in database. Innodb is a better option for processing large volume of data. It gives good support for ACID property, however there are some cases where the InnoDB falls short of ones expectation. Some common examples includes 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 placeholder for items in queue, fragile data pulled from Webservice by making API call.  Now if you are aware about this already, you will think mysql engine can be changed for a table using 'ALTER TABLE tablename ENGINE = MyISAM' or using phpmyadmin UI from operations tab.

Custom Tables are created in Drupal using hook_schema(). It is possible to add some extra key => value to 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',
);
'mysql_engine' => mysqlenginename.
 
Using the above code, you can create table with MyISAM engine, I hope this gives you better understanding about usage of MyISAM and mysql_engine.

Comments

3 Comments

MySQL has performance problems if you JOIN an InnoDB table and a MyISAM table, so you might as well keep everything the same.

Mixing MyISAM and InnoDB tables can be problematic. I had issues a few years go with joins between the two types of tables, and I see from http://dba.stackexchange.com/questions/35322/how-does-mysql-deal-with-qu... that this has not been solved. Be careful if you try to do it, and test carefully!

sivaji's picture

@Damien McKenna and @psteve, thanks for the pointer. That makes sense, in fact we thought about the same though not sure if that could be a problem. Anyway we use it for custom queueing and reports tables used to populate facts from Mandrill Web Services. We see no use cases to do join with Drupal innodb tables.