Drupal MySql database design question

Question

I was just looking at the MySql database created by drupal after I installed it.
All the tables are in MyISAM.

With a complex software like drupal, wouldn't it make more sense to 
use foreign keys and hence InnoDB tables to enforce referential integrity?

Without foreign keys all the constraint checking will happen at the 
PHP end.

Answer 1

MySQL offers a variety of database engines for a reason - different engines offer different advantages and disadvantages. InnoDB is a great engine that offers referential integrity as well as transaction safety, but it is poorly optimized for the use case of web site where you have order of magnitude more reads then writes.

MyISAM offers the best performance for a web site where most hits need only read access to the database. In such cases referential integrity can most often be maintained by writing your data inserts and deletes in a way that they cannot succeed if they compromise integrity. For example, instead of writing

DELETE FROM mytable WHERE id = 5

you can write

DELETE mytable FROM mytable LEFT JOIN linkedtable ON mytable.id=linkedtable.ref WHERE id = 5 AND linkedtable.ref IS NULL

This will succeed in deleting the row only when the are no external references to it.