Home » Groups » Database

MySQL Table Type

public
groups: Database · MySQL
webappl - Tue, 2008-01-01 23:57

I think it is safe to say that many developers already using or about to start using MySQL table type InnoDB. Some developers even changing default MySQL storage engine to InnoDB.
In many cases it make sense to convert table type if not for all tables, then at least for some tables.
In this situation will be nice if modules developers will state what table type is necessary for those modules and why.
Otherwise, its has to be investigated and tested for all modules in the project installation. May be it is fun but it is also a lot of time.
My suggestions for Drupal developers are

  • include into modules .install file the indication of MySQL table type in the end of each table creation query like "CREATE TABLE `tablename` (...) ENGINE=InnoDB DEFAULT CHARSET=utf8" or "... ENGINE=MyISAM ...";
  • include into 'INSTALL.TXT' file description of why these tables need to have those types.

That will give another developers enough information for making right decision on MySQL optimization of project's Drupal installations.
Otherwise, it has to be investigated for each table.
Please share your experience.

Alex Pandre
Custom Drupal modules and themes development, SVN for Drupal environment development, Code optimization
Join MySQL group and post your suggestions in related groups in the same time

» Login or register to post comments
 

Schema API

Crell@drupal.org's picture
Crell@drupal.org - Tue, 2008-01-08 15:56

The Schema API has made CREATE TABLE statements obsolete as of Drupal 6. If such a feature were added, it would need to be an additional array property on the schema array.

That said, I'm not sure that's something the module author should be specifying. Many web hosts do not support InnoDB tables, as they take up more space. One also needs to tune the main database settings differently for caching, memory limits, etc. in order to get the most out of InnoDB. There is also a sometimes complex interaction between tables of different engines when joining that can eliminate the benefits of InnoDB or MyISAM.

This is really something best left to the system admin, not to each module developer.


Login or register to post comments

I'm talking here about MySQL optimization for complex web sites

webappl - Tue, 2008-01-08 19:46
  1. I'm not familiar yet with Drupal 6 but I do with Drupal 5. And I'm talking about way for module's developers to make sure that MySQL optimization for they modules will be easier.
  2. If I'll have to depends on what web host providers ready to give me, then I will not be able to create any serious web sites like social network with rich functionality over many modules since host providers usually limits PHP sessions to 8MB. So, of course I'm talking about dedicated servers where you are in charge of your web applications and server side software installation and configuration.
  3. In order to make right decisions on MySQL optimization of your Drupal web application you have to understand on developer level how modules interact with tables in database. So, unless your system administrator is also a Drupal module's developer, he will not be able to do any good for MySQL optimization of your Drupal based web application.

Alex Pandre
Custom Drupal modules and themes development, SVN for Drupal environment development, Code optimization
Join MySQL group and post your suggestions in related groups in

Login or register to post comments

MySQL has default table types

markus_petrux - Thu, 2008-05-22 02:53

Aside from MySQL server defaults, I would love to see an option at Drupal core/module installation time to decide table types.

OTOH, I think it is not correct to let Drupal developers decide which is the best table type for a particular table, which is what happens when this option is specified in installation schema. If so, I would let developers advice about that, so the one installing may have a hint on what to decide.

Login or register to post comments