My Love Affair with MySQL

For many years, I did the bulk of my development on Access. It was quick and easy to develop on and with the Upsizing Wizard, I could easily upscale it to SQL server when needed. I ran the cfwebstore.com website and demos on it as well, and appreciated how easy it was when I needed to move my sites....just grab a copy of the database and move it over to the new server.

As CFWebstore grew and became more database-intensive, I've definitely found myself moving further and further away from Access. As my site got busier, it started to have more problems, with timeouts and failures happening often on a daily basis. As I've needed more control over things like indexes and foreign key names, I've gotten to appreciate how well-hidden Access keeps such things. Upscaling doesn't always work properly, and trying to write scripts to upgrade Access databases were an incredible pain due to its failure to handle scripts. Security as well is more of an issue, requiring me to set up an separate location for my Access files and password-protecting them.

But neither was my site busy enough to really warrant moving to a SQL Server plan which is often a pricey option. SQL Server Express is becoming more common but still somewhat limited. I needed an option that was cheap, but more robust than Access. Enter MySQL.

I wasn't really paying much attention to MySQL until a few years ago when a version was finally released that could handle more complex SQL constructs like sub-queries. Up until then, it was kind of a low-quality open-source database option for Linux that was too limited to be of much use. Since then, it's grown into a mature database platform that is now one of the most commonly used on the internet, both for Linux and Windows servers. Many hosts now offer free MySQL databases with hosting plans and anyone can download the server and install it for use in development or on a dedicated server.

If you are completely new to MySQL and have only worked with Access, it may seem a bit scary at first to switch. But as MySQL has grown in capabilities, so too has the ease of working with it. Version 6 of CFWebstore is compatible with MySQL out-of-the-box. No longer do you have to tweak some of the files to get things like bit fields to work correctly, everything is handled from the single configuration file that sets your database type. If you have a host that provides you with MySQL, you will most like have a copy of phpMyAdmin which lets you administer your MySQL database right from your browser. You can open up a script window, load in the webstore installation script(s) and run them to create your database.

An even better option are the various tools for working with MySQL directly from your desktop. I personally use Navicat. It has a nice, clean interface that is easy to use, and provides a variety of data and structure synchronization options that I use on a daily basis. Each night it downloads backups of all my remote databases, and I also have it set to reset the demo stores from a backup copy so that they are returned to the defaults from whatever test users have done that day. What a great time savings over doing all this manually! And when I've needed to move my sites to a new server, it's a piece of cake to just do a sync function from the old to the new server to copy over my databases.

Navicat though is one of the more expensive MySQL tools. For a cheaper option, you might want to take a look at SQLyog which is very popular and about half the price. And for free, you can try TOAD for MySQL. I don't particularly care for the TOAD interface, but hey, for free, it's hard to argue! There are plenty of tools to help you convert your stores from Access as well. Many of these tools have a conversion or import option that may work, but be sure to check and make sure they properly import all the indexes, foreign keys, defaults, etc. as often they miss one of more of these features. For a really solid conversion tool, check out the DBConvert product line. While they aren't free, they are really solid tools that seem to do a good job of accurately converting the database structure and data.

If you are one of those still using Access to run your live stores, hopefully this article may have sparked your interest in trying other databases like MySQL. You'll have a store that runs better and faster as a result and you may find that development and maintenance is even easier than you thought it would be!