WARNING: Geek related material follows.
I’ve been running MySQL ever since I started programming PHP (about 6 years) with very few complaints. It’s fast, supports most of the SQL you’d need to use and had great support in PHP. Lately I’ve found that I can’t ignore some of the features that MySQL lacks. Some of the lacking features in MySQL have been addressed with recent releases (ie. subselects and transactions). Some features are planned for the 5.0 release (4.0 just went “gold” about a year ago). Here is a simple list of my complaints with MySQL:
- MySQL “supports” transactions in its InnoDB tables, which is fine, except InnoDB tables do not support FULLTEXT keys. This means you either mix and match table types or live without FULLTEXT keys. I’m fairly sure that you can’t join InnoDB tables in a query that includes FULLTEXT syntax, but don’t quote me on that.
- MySQL doesn’t plan on supporting stored procedures or views until version 5.0.
- MySQL does not support object-relational features, such as table inheritence (a feature I really need and want).
- MySQL does not support constraints nor does it look like there are any plans to add such support.
- Stored procedures will show up in version 5.0.
A friend of mine recommended I check out PostgreSQL because of its support for many of the advanced features that MySQL does not support. I was instantly blown away by the simple fact that it supported object-relational features, specifically the much sought after table inheritence feature. The fact that PostgreSQL supports this along with views was enough to make me take another look.
Combine this with the upcoming release of PHP5 and the resulting need to rewrite my application framework and you have good reason to migrate database platforms. I’m currently in tentative testing of PostgreSQL and should have a final decision soon. For now it’s looking good that PGSQL will become my DB of choice. Once you move past simple SQL and into the realm of complex content management I’m not sure one can ignore the inadequacies of MySQL. Please post your input if you have any (don’t bother commenting on < 7.0 PGSQL as I’ve read the differences between those series are dramatic).