MySQL 5.0 - A quick look

I've started seriously playing with MySQL 5.0 as an upgrade from the 4.0 that we've been using for a long time now at work. And I'm liking what I'm seeing.

Some people will claim that other databases have had features for years that are just now going into MySQL. I'll agree with that, though no other databases have had the same momentum and open source traction that MySQL has. The closest open source competitor, PostgreSQL, is more feature filled but still doesn't seem to have the following that MySQL has.

Let's look at a few of the new goodies.

Views

One of my favorite additions - basically now we have the ability to create pseudo queries on the fly and abstract the model from the person requesting it (MVC...ring a bell anyone?).

In the older MySQL I ended up having to use temporary tables to set up the queries to pull the data together. It was getting so difficult and painful that I eventually did switch to PostgreSQL for this particular set of database logic. But we were so heavily rooted in MySQL for the other stuff we did that I couldn't complete the switch all the way. So I'm excited about being able to go back to 100% MySQL.

I've played with views and they're quite easy to set up - very SQL standard like. PHPMyAdmin recognizes views with a separate icon from tables and lets you search them accordingly. Very handy.

Stored Procedures

Another great missing feature. I was able to add some functionality to my database backend that did some of the querying for me without me having to worry about specifying it each time.

For example, instead of performing a query on a table to figure out which project was the latest and greatest, I can just use the GET_CURRENT_PROJECT() function in my SQL statements now. I even made it take a timestamp so I could see what the current project was :

GET_CURRENT_PROJECT("2005-01-01 12:00:00");

and it returns the results accordingly.

This makes life much easier, instead of me having to maintain the logic in my code I can maintain it in the database. Who wouldn't want that?

Triggers

Now I can perform an action when data is inserted and updated in my database. This is handy - I can make it update other tables as well - legacy ones that have lots of data in them that needs to be formatted and parsed just so.

I'm excited about 5.0. I've been running 5.0.16 now for a few weeks with no signs of crashes or data loss. It's not been rolled into a production server yet, but it will be before long.

The bad...

In playing with these new abilities over the past few weeks, I've come to notice a few flaws. First, they're not *fully* implemented yet. There's still a large subset of things that just "don't work" right now. The claim is that it's being worked on, but the amount of functionality that still doesn't exist yet is pretty big - in my opnion.

And second, the documentation is still lacking. The official online guide has been updated to reflect these new features, but it's lacking examples as to how to pull them off. Online books and guides haven't been updated yet. The best documentation available right now are a set of PDFs by Peter Gulutzan, a MySQL engineer, and even then those are still not what I would consider...superb.

So, Virginia, it looks like the proverbial MySQL Santa brought a new bag of goodies this year for us to play with. And while they're not revolutionary in the SQL world, they are welcome additions to any MySQL administrator's bag of toys.

MySQL is slowly catching up

Thanks for a great post. I think I read recently that Oracle's revenue is down a bit. I imagine that SQL Server and MySQL are coming at it from both sides.