MySQL Kinks

InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine that has commit, rollback, and crash recovery capabilities. InnoDB does locking on the row level and also provides an Oracle-style consistent non-locking read in SELECT statements. These features increase multi-user concurrency and performance.

Sounds pretty good, right? Everyone likes to be able to rollback changes and have crash recovery capabilities. But the real deal-breaker for me is support for foreign keys.

InnoDB also supports FOREIGN KEY constraints. You can freely mix InnoDB tables with tables from other MySQL storage engines, even within the same statement.

This is very important to me, because in my current database course we focus on normalization and employing foreign keys. Our professor does not think highly of MySQL and during the first few classes he would put MySQL on the same level as say, Microsoft Access. Pretty low opinion!

But perhaps it’s bad reputation is deserved? Dare I say that?

The reason it’s perhaps deserved is that even today, MySQL by default creates MyISAM tables which do not support foreign keys. So all that fancy referential integrity doesn’t get put into action. You can define foreign key relationships in the MyISAM tables and the DB engine won’t get in your way and say “Hey wait a minute!”

Either way, I’ve switched over my stuff to InnoDB and I couldn’t be happier. I’m enjoying using foreign keys with RESTRICT and CASCADE. Good stuff.

I have a pet project that I’ve been fooling around with and I’m very proud of what I’ve done so far. I’ve had quite a bit of trouble with the way Wordpress handles my code posts, to the point where I’ve decided it would be better to just develop my own lean and fast code blog using PHP and MySQL and that I can toss all my completed code into and just provide links to entries here.

It’s a good project to do, because I’ll learn how to create my own blogging system, and it’ll serve as a good primer before diving into Wordpress.