MySQL Gotchas

Geek blog [Slashdot] has been abuzz since it was announced that [MySql], darlings and key ingredient of the [LAMP] explosion, is doing business with SCO, the absolute scourge of the Linux world.

Now that MySQL has been associated with the devil, some folks are giving a longer look at [PostgreSQL]. While Postgres isn’t quite as ubiquitous as MySQL, it doesn’t suffer from the reputation of being something of a “toy” database.

MySQL has some big advantages going for it- is very easy to get off the ground and running, and there is a wealth of resource and communtiy support easily available. I cut my teeth on MySQL, and I loved it. I was able to do relatively sophisticated things with it in a very short time. The big disadvantage is the “toy” aspect, brought about largely by the combination of being somewhat non-compliant to SQL standards and its lack of high powered features common on some of the established database systems.

When I got my great-big-professional-job at a company that is heavily invested in RDBMS technology, I didn’t get the feeling that my interviewers were overly impressed with my previous MySQL projects. My little homegrown darlings weren’t treated with outright disdain, but it was clear that they would have been more impressed with something more robust, both in scale and in the power of the tool.

To tell the truth, once I got into the trenches at my new job, I understood. My SQL kung-fu jumped several levels of sophistication. Working with hundred column tables with millions of rows on Oracle 9i with PL/SQL on a high powered UNIX box was quite a step up from my previous endeavors.

This article isn’t about Oracle, but Postgres vs. MySQL. To that end, I came across lists of “gotchas” detailed on [sql-info.de]. These lists are made up of things that can trip up a user moving from one database system to another, and things to be wary of in general:
[MySQL Gotchas]
[PostgreSQL Gotchas]

A casual comparison of the two lists reinforces the widely held notion that Postgres is much more standards compliant than MySQL. The Postgres website touts itself as “…the highly scalable, SQL compliant, open source object-relational database management system…”, where MySQL is content with the succinct “world’s most popular open source database”.

An in-depth look at the lists left me with shaking my head at both MySQL and Postgres. Postgres seems to be the better of the two in minimizing unexpected behavior, but some of the ‘gotchas’ from either system could be devastating to the data one cares enough about to enter into a DB table in the first place!

To be fair, some of these are errors that have been fixed in the latest versions, and some are scheduled to be fixed in the versions that are coming out in beta form at the time of this writing.

The verdict? Assuming it is installed or installable on the servers folks want to use, it would seem the Postgres is the database of choice for those who want a compliant, robust database.

For the vast majority, MySQL probably delivers more power than is needed. My guess is that the bulk of web apps with a database backend simply aren’t that big. MySQL can easily handle the little stuff. As for the big stuff, Slashdot serves 80 million pages per month and it is run with a MySQL backend, albeit with some serious tuning/tweaking.

MySQL is easy for the beginner, there are easy to find reference books and materials, and most importantly, MySQL probably trumps Postgres in the category of installations among ISP’s. Not only do most webhosting outfits have MySQL databases “pre-installed” as part of the package, they usually include goodies like the simple, popular web based tool [phpMyAdmin]*. All of this usally comes “built-in” from the ISP, making it simple for the person who does not want to deal with the details, but just wants to get something done.

How many of those MySQL using folks are going discover later on down the road that they need/want a more standards compliant database? Not many, maybe 1% or, at a stretch, 5% tops is my guess. Where do these claims come from? I pulled them from my hindquarters, of course. YMMV.

*Yes, Virginia, there is a very similar tool for Postgres called [phpPgAdmin]. The point to focus on here is the number of webhosts who have both Postgres and phpPgAdmin installed, vs the number with MySQL/phpMyAdmin.

This entry was posted in oracle/sqlserver/database. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *