For fun, the other night, I did a quick porting job on a company's web based J2EE product moving the database from MySQL to PostgreSQL. The performance gains were instantaneous. Creating and dropping indexes without locking up the product is always a bonus. Queries are faster, concurrent inserts are faster, deleting rows was faster, all around there were big improvements. I'm not sure they'll make the institutional change, but it is at least a proof of concept to make the argument if necessary.

So why do developers use MySQL? This is something I can only assume is a comfort issue. MySQL's "stickiness" or "vendor lock" is very much similar to that of Microsoft Windows. yes, there are alternative systems that would be better (Ubuntu or Mac for instance compare very well), but moving there means getting out of your comfort zone. In a purely merit based discussion where each side has clear pros and cons, Windows almost never holds up against other systems on the weight of its benefits without extenuating circumstances or 3rd party requirements. Similarly, in my opinion, MySQL holds up even less.

The conventional wisdom is that MySQL like Windows is what everyone uses. This is a strong discouragement for developers to experiment with different systems. Changing an underlying system tends to be a very disruptive process.

While developers need to test on multiple web browsers to make sure that their code functions correctly on IE, Firefox, and Safari, these same developers seldom test or design their data access patterns for portability for their database platform. Just like idiosyncrasies of one browser will cause your code not to work on others, designing for one database means that your product has probably been written in a way that make switching databases difficult or impossible without dome degree of modification.

MySQL has a number of functions and language enhancements that are not part of a standard SQL specification. So, while using more standard constructs would probably be about as easy as using a MySQL specific function or pattern, many developers, not thinking about portability, end up using the non-portable methods without even realizing it.

In my little experiment, out of five queries and one insert, I had to change four of them. Most of them were what I would classify as haste mistakes. Having the database do something it didn't need too. Using a non-portable form over another more portable syntax, basically little things, but little things that add up in porting time.

In short, I encourage every developer to try to write their database code with N databases in mind, N being greater than one. Try it, you'll be surprised how much you learn on your first port. You'll become better at what you do!