September 7, 2005

Of Databases and Keys

I've been working on two sites over the last week or so, doing a review of their structure and implementation prior to developing new features. Neither of these sites were initially developed by us.

I've noticed an interesting coincidence. Despite being written in wildly different technologies (one is PHP, the other is Cocoon), and serving different purposes, they both have MySQL back-ends. The interesting coincidence is that neither of them make use of foreign keys, and not because they don't need them.

From what I can tell, the default storage engine for MySQL databases is MyISAM, which does not support foreign keys:

For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it.

... so maybe that's why they are missing.

Foreign keys are available in the InnoDB table type. Perhaps it's because foreign key support was only added to InnoDB tables in version 3.23.44, back in October 2001? Seems unlikely, since both of these sites are newer than that.

Maybe users of MySQL got so used to lacking foreign keys over the years that it became habit to handle this sort of relationship at the application level rather than the database?

Urgh. MySQL, pushing databases into the dark ages since 1995. I wonder if there's a MySQL-to-Postgres-and-fix-the-tables-while-you're-at-it script out there? ;-)

Posted by savs at September 7, 2005 1:39 PM
Comments

querying InnoDB is a lot slower for than MyISAM in a lot of cases, which may be the reason.

Posted by: Tom at September 7, 2005 2:10 PM

InnoDB tables don't support MySQL's fulltext search, which is very handy, and used by a lot of sites. I generally try to isolate the stuff I want to fulltext index into a MyISAM table, and put the rest into InnoDB, but sometimes that's not practical.

Posted by: Steve Michel at September 7, 2005 5:26 PM

InnoDB is still slower than MyISAM, and MyISAM is the default, lots of people just don't know that you can do real foreign keys using InnoDB, and you can't gaurantee that a particular mysql server even supports InnoDB (I had to enable it on a recently installed system), it's by far not a default option. So you deal at the application level when using MySQL because there's very few other garantees... of course, the correct fix is to use PostgreSQL instead, and forget the limitations that MySQL sets (and have views supported, and sub selects, and many many other really useful things that MySQL is only just getting round to implementing...)

Posted by: Brett Parker at September 8, 2005 12:39 PM