Should I upgrade to MySQL 5?

So we recently installed a server with MySQL 5.1 and this prompted me to write an article about it...

Why did we do it? Well why not, we were starting a project from scratch so we could benefit from the features without the worry of breaking something.

Before you Upgrade
Do yourself a favor and find out what was added and what is no longer supported.

Here is a link where you can read what was added to MySQL 5.0:
http://dev.mysql.com/doc/refman/5.0/en/mysql-5-0-nutshell.html

And how about what's new in MySQL 5.1:
http://dev.mysql.com/doc/refman/5.1/en/mysql-5-1-nutshell.html

If it aint broke don’t fix it. If you do not need to upgrade then you probably shouldn't. Some performance improvements may allow you to improve the speed of your application or you may take advantage of added features but if you have already designed an application and version 4 is capable of handling the app then just plan it for your next release instead. Some subtle changes may cause some unexpected results.

What I like about 5
Personally I take advantage of the sub selects more now that MySQL 5 supports them. I know views are supported as well. Haven't had to use those yet. Stored procs is a huge addition. The optimizer seems to do a better job with joins and finding the best keys to use. Using the "EXPLAIN" in queries yielded the same performance results as forcing the index used. This almost eliminates the need for forcing the use of a particular index. If you notice something horribly wrong with the explain, check the data first. Check your query and table structures second and then maybe you can blame the optimizer. Most times when I would help people, it was a problem with redundant data, forgot to add index, rarely was it the query optimizer. The InnoDB storage engine was improved and based on some bad past experiences this is good news. The storage is more compact and recovery times do seem better.

Final Thoughts
If your using shared hosting and they are still using MySQL 4.1 for your server then ask them if they can move your account to a MySQL 5.0 server. My hosting company was happy to do so and i picked up PHP 5 while I was at it. Some companies are reluctant since they are using applications to manage shared hosting accounts on servers. Unexpected results can really cause problems when you have many hosting accounts on a server and decide to upgrade. It may not only cause problems for their applications but what about all the customers hosted on these servers that built code based on earlier versions. So don't expect them just to upgrade you without you asking.

---

Brian J. has been involved in web design since 1997. He is the founder of True Vision Computer Services, Inc. His recent focus has been on web applications and information systems development.