Data Management

Many Web developers prefer MySQL

As database technology evolves, a growing number of developers are choosing MySQL as the most stable and efficient application for server programming. Take a look at the strengths and limitations of MySQL and see how it stacks up against Microsoft Access.


By Susan Sales Harkins and Martin W. P. Reid

Change is the operative word in the IT business. If management asks for a list of database recommendations, chances are the items on your list will become outdated before you can implement them. Subsequently, you may need to reconsider the software and hardware that you:
  • Use to get your work done.
  • Purchase so that others can get their work done.
  • Develop so that others can get their work done.

No matter how up-to-date your information, something on your list will usually be obsolete before you can route your suggestions and deposit any checks. Fortunately, you’ll face no blame or recriminating complaints; it's simply the nature of the beast at work. Database technology often changes before you can even integrate it.

To cope with the fast pace of evolution in database technology, an increasing number of programmers are switching from desktop database applications, such as Microsoft Access and SQL Server, to MySQL. Although MySQL isn't serious competition for SQL Server, many service providers now support MySQL as an inexpensive and effective alternative.

Reasons to consider MySQL
If you're in the market for a reliable database application to support your Web development work, MySQL should be on your list for the following reasons:
  • It's inexpensive (often free).
  • It has less network overload.
  • It's highly optimized.
  • It makes applications easy to back up.
  • It provides flexible interfaces to many different data formats.
  • It's easy to learn and quick to implement.
  • It features affordable support.

$$$
Simply stated, you won't find anything cheaper than MySQL. In fact, for most users, MySQL is free. A modest licensing fee is sometimes required, but this fee generally applies under only two conditions:
  1. Embedded usage of the MySQL server
  2. Commercial applications that work only with MySQL

The Windows version of MySQL server, for example, requires a license. At just a bit less than $200, however, MySQL licenses are still much more affordable than any other database application. Office XP Developer retails for $799 for new users and $549 for upgrades. Access 2002 is $339 for new users and $109 for upgrades.

Avoid network tension
For simultaneous user support, there is no comparison between Access and MySQL. Access begins to short out at about 15 simultaneous connections, and we've heard complaints when the number is as low as five. This is not to suggest that only five users can connect to an Access-supported site at the same time. Simultaneous connection actually refers to concurrent processes. As such, Access can actually handle unlimited connections, as long as those connections remain under the process limit. Read-only sites (which really aren't as rare as you might think) can support up to 255 users. Larger sites do inevitably upgrade to SQL Server to improve performance and stability.

In contrast, MySQL's maximum connection default is 100 users. And while one should never base performance evaluations on a program's default settings, we haven't heard any complaints from users relying on MySQL for large sites with heavy connection play. In addition, plenty of traffic doesn't seem to affect query optimization significantly.

In tests with equivalent hardware and file sizes on a Windows 98 system, MySQL consistently performs faster than Access 2000—but not always. Updates show the largest discrepancy in the area of performance, with Access often requiring twice as long to complete the same task. When you're dealing with small amounts of data and fast systems, this discrepancy isn't noticeable. It becomes a problem only when handling hundreds of thousands of records. MySQL loses to Access only when dealing with object structure rather than data. When creating a table and an index, MySQL locks out the table, which slows things down when working with large amounts of data. This latter issue isn't really a typical concern in Web programming, though, where hits and querying matter more than data storage. In this area, MySQL wins.

Optimization
It's safe to say that MySQL optimization issues will stem from your hardware configurations and from not MySQL itself, which isn't true with Access (and other desktop applications). While the Microsoft Jet Database Engine is efficient, it isn't the fastest ride in town. Throw in a badly designed database, and your site can come to a screeching halt.

Design also affects MySQL. For instance, MySQL doesn't support foreign keys, a flaw that will affect your design and your site's performance. With your MySQL site, keeping disk activity to a minimum, securing a rapid CPU (or CPUs), and supporting appropriate bandwidth will matter more than the actual database design and/or query activity. In fact, some developers call MySQL the fastest database available. On the downside, MySQL does groan when you have many tables requiring lots of transactional updates at the same time.

Backup
If you have ever struggled with a corrupted MDB file, you'll appreciate MySQL's advantages over Access in this area. First,mysqldump produces a much better and more reliable backup than the Access process of simply copying an MDB file. Second, even if the MySQL backup is partially corrupted, it's much easier to restore than a corrupted MDB file.

Scalability and capability
In the language of mountaineering, pitting an Access database against a MySQL database is like comparing a gently rolling hill in Indiana to Pike's Peak. The simple fact is this: MySQL can handle much larger files than Access can. If you harness Access users to a 100-MB MDB file, you should prepare to log performance complaints. A similar database in MySQL will show no signs of overload.

MySQL also features an impressive versatility that enables it to interact with a number of interfaces, including command-line clients, Web browsers, and various programming interfaces such as C+, Perl, Java, PHP, and Python. You can use a prepackaged client or write a custom application. You can run MySQL on UNIX, Windows, and OS/2, so it's available to both PCs and servers.

It's true that the Microsoft ActiveX Data Objects Library (ADO) has made Access more flexible in the foreign data market. ADO permits you to retrieve data regardless of its location and then present that data in a common interface: the browser. On the downside, ADO is fat (a resource hog) and learning it requires much time and effort, even for the competent developer or programmer. No one picks up ADO in a day.

The learning curve
If you're already familiar with database technology, you're practically home free. The database-savvy user will be adding MySQL to his or her resume before the day is out. In comparison, Access is a much more complex database and development tool. Even the competent developer will require some time to become proficient enough to put the program into action.

As you might expect, MySQL supports Structured Query Language (SQL). If you already know a version of SQL, so much the better. Developers with a VB or VBA background may find their experience with ASP helpful in shortening the learning curve.

Support
Good, free support just doesn't exist anymore. However, MySQL does host a number of mailing lists. A few are technical in nature, and members offer one of the best forms of support available—they share their experiences and expertise. In addition, you can purchase support options that include e-mail and telephone help. For the most part, support rates aren't fixed, so it isn't possible to have prices quoted.

What MySQL can't do
Access is a relational database management system (RDBMS), and MySQL isn't in the same league on all levels. This means that, as good as MySQL is, it isn't the best solution for everyone. The following list documents some of the relational and management features that MySQL doesn't support as this time:
  • MySQL can't handle complex relational issues such as subselects (subqueries), although most subselects can be rewritten as joins. We expect this feature to be included with the next version.
  • Transactions and commit/rollback is another management feature that MySQL doesn’t directly support. A transaction is a group (or set) of tasks executed together as a unit. If one transaction can't be completed, none of the transactions is executed. Commercial sites with online ordering capabilities will find this lack of transaction support disappointing. MaxSQL, a separate server, supports transactions through the use of extraneous tables.
  • Foreign keys and referential integrity allow you to relate data in tables and then place constraints on what can be done to that data. So an application that depends on complex relationships might not be a good candidate for MySQL. When we say MySQL doesn't support foreign keys, we are alluding to its referential integrity; MySQL won't enforce foreign key constraints, nor does it support cascading deletes. In short, if you rely on complex relationships, stick with Access.
  • You won't find stored procedures and triggers used in MySQL. (These are comparable to Access event procedures.)
  • Access's GetRows feature provides better data retrieval.

The long and short of it
Table A  gives you an idea of how MySQL, Access, and SQL Server stack up:

Table A


If you have complex database needs and are blessed with plenty of resources and money, go with SQL Server. If your needs are equally intricate but less easily supported, try Access. Everyone else should at least give MySQL a look.

Susan Sales Harkins writes for CNET Builder.com and is a consultant specializing in Microsoft Office. She is the author of Using Microsoft Access 97 and Using Microsoft Access 2000, both published by Que.

Martin W. P. Reid, also a contributor to CNET Builder.com, is an analyst and programmer at the Queen's University of Belfast. He lectures in relational-database design, and in his spare time, he acts as a database consultant for several small businesses in Northern Ireland, using various versions of Microsoft Access.

Editor's Picks