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).
PostgreSQL can go in the can that Crash takes a dump in! 😉
Hello there…
MySQL 5.0 alpha has now a object-relation system in place. PHP5 comes built in with the new MySQLi (MySQL improved) extension. I still prefer MySQL over PGSQL, mainly because whatever MySQL is lacking (of which is little now with version 5), they will have in the future. What matters just as much if not more, is the perfomance. MySQL is pre-dominantly faster in my experience with both DBMS.
Not to say PGSQL is not fast, I mean it has won Linux Magazines “Best DBMS of the year” awards two years running now…
If you want raw speed and lowest overhead while still supporting SQL, try SQLite instead. It’s faster than MySQL and more compliant to the SQL standards.
As far as other speed issues, remember that the fastest database call is the database call you don’t have to make — caching is the key. It doesn’t matter what database performance is like if your application is poorly architected.
That said, MySQL is a steaming pile. Aside from the gotchas like ‘a purple cow does not cry’ going into integer fields without throwing a warning let alone an error (http://sql-info.de/mysql/gotchas.html), its lack of features should relegate it to the dust bin. If you don’t need the features of PostgreSQL today (or Firebird or any of the other capable database systems out there), that’s fine. But deriding them because they have features you don’t need today is like refusing to use a programmign language because it allows constructs you don’t ever need.
You may not need check constraints now, but using a database system that accepts the CHECK syntax, doesn’t even throw up a warning that it lacks support (important if you’re migrating from another database system), and has no immediate plans to implement check constraints is… foolish.