O'Reilly Network    
 Published on O'Reilly Network (http://www.oreillynet.com/)
 See this if you're having trouble printing code examples

YourSQL Database Might Just Be MySQL

by John Paul Ashenfelter

Related Articles

Are PHP and MySQL the Perfect Couple?

How PostgreSQL Rose to Fame

So you need to build a database application that will be delivered over the Web. Your users have the browsers, you've got the web server and application software, but you need a database. What are your choices?

There are the desktop choices -- Microsoft Access and Foxpro, dBase, FileMaker Pro -- but they're nowhere near powerful enough. Then there are the big guns -- SQL-Server, Oracle, Informix, IBM DB2, Sybase -- but who can afford the $5,000-$50,000 fee just to get them on the development server? What do you do? If you're like many developers, you'll probably turn your gaze toward the open-source community. While there are several open-source databases (see sidebar), one of the most powerful and most popular, particularly for web-based database applications, is MySQL.

Other popular open-source database tools include:

• PostgreSQL is the most advanced open-source database tool. It offers object support, transactions, triggers, a procedural SQL language, and a number of other high-end features.

• mSQL (also known as MiniSQL) is less sophisticated than MySQL, but is extremely fast with a small footprint. It also has a standalone scripting language (Lite) as well as web-oriented scripting (www-mSQL).

•  GNU SQL is the db offering from the core open-source organization. It's aimed more at client-server applications and C programming than web database development. It's also in the late beta phase, so is probably not ready for production environments.

• Beagle is another beta open-source database platform that currently is far from finished. But it does have great historical documentation of what it's like to build a database tool.

The fundamental reason that an estimated half million servers around the world run MySQL is that it provides an incredible amount of database power at a cost that is zero in most cases, and a few hundred dollars in others. But another strong reason that MySQL is chosen to power web-based database applications is PHP. As Jason Gilmore discusses in the companion article, PHP is a powerful web scripting language that has strong ties to the MySQL database. Both tools are powerful in their own right, but together they can compete with any web-database tool on the market.

Brief History of MySQL

MySQL was developed by Michael (Monty) Widenius at the Swedish company TcX DataKonsultAB in the mid-1990s. In some ways, it was developed to provide functionality that was missing from mSQL, one of the few open-source databases available at the time. But the underlying goal was to provide a solid platform for delivering web-based applications. The initial tool was developed in May of 1995 and later released to the open-source community. Since that time, MySQL has been ported to a wide range of operating systems and been the focus of an intensive community development process that has resulted in a number of related tools and applications.

The latest stable version of MySQL is 3.22, though the alpha 3.23 release is in its 17th iteration at this writing and probably close to full release. Binary distributions are available for Red Hat (RPM), Windows, OS/2, Unix variants for MacOS X, HP-UX, Intel-optimized Linux, FreeBSD, SGI Irix, and Alpha-based Linux. There are also user-contributed versions for a wide range of operating systems and full source for building MySQL for an arbitrary operating system.

Features of MySQL

Just because MySQL is basically free (see sidebar on page 1) does not mean that it has a fundamentally limited feature set. MySQL is an excellent database for mid-range web database applications. Existing installations currently handle dozens of databases, thousands of tables, millions of records, and tens of gigabytes of data, which covers the majority of web applications.

In all honesty, it is missing a few significant features compared to its high-end competitors (discussed in the next section), but the original design goal of producing a fast, robust, and easy to use database has governed the decisions about what features to include and omit. More importantly, users have a strong voice in which features get added to MySQL, as well as the ability to add the functionality themselves.

Technical details

MySQL was designed from the ground up as a multi-threaded database engine, an excellent architecture for both multi-user environments (like the web) and multiprocessor servers. The database engine is written in C and C++. Database files use B-tree disk tables with index compression and are accessed using ISAM (indexed sequential access method). While these features need not directly concern you, they add up to very fast database performance as well as a high degree of reliability.

For the hacker, its good to know that MySQL is developed using GNU Automake, Autoconf, and libtool for portability. The source, a C compiler, these tools, and the desire to add a feature is all you need to customize MySQL to your own specifications. Extensive version histories and planned enhancements are available at the MySQL web site.

SQL implementation

Regardless of how good the guts of the database are, the quality of the SQL implementation is what's really important. While there are a series of SQL standards, database vendors tend to pick and chose a subset of the functionality in the standard when they implement it. Vendors also typically add their own extensions specific to the needs of their users.

The folks at TcX, in an effort to provide a fast and robust database, have focused on the core functionality used by the majority of users and added a few tricks of their own to speed up common tasks. Basically, MySQL offers the core functionality of ANSI SQL (SQL2) within certain limits:

Most plain vanilla SQL should work fine in MySQL. Deeply nested clauses and esoteric functions are the most likely points of failure if you are porting SQL code from another database to MySQL. And, of course, proprietary extensions from other languages will fail. But in my experience, translating SQL from routine applications built in other database to MySQL is a fairly painless process.

Things missing from MySQL

Speed always demands a performance price in most computer applications, and MySQL is no exception. Two of the most fundamental high-end database features missing from MySQL are transactions and stored procedures (including triggers). Both of these features, particularly transactions, require an amount of database server overhead that can noticeably impact performance. In the interests of speed and robustness, these features have been omitted. It's worth noting that some high-end database features other than transactions and stored procedures are also missing; for instance, Oracle's ability to calculate dates in Roman numerals.

Some other, more germane features that are missing in MySQL (and currently near the top of the TcX TODO list) are:

Some of the missing features may be ones that you consider crucial -- if so, MySQL won't work for you. You may consider rearchitechting your application to leverage what MySQL can do. Or you may want to check out PostgreSQL. You may even need to grit your teeth and buy a high-end tool. But for the rest of us, MySQL is a powerful database.

MySQL is open-source software, but it's not technically freeware in all cases. There are a small, but clear set of conditions where you need to buy a $200/server license for MySQL. Basically you need to buy a license if: you make money from selling MySQL or charging to install or maintain it.

TcX also currently requires that you buy a license if you use the Windows version to help defray the additional costs of porting to the Windows platforms. You are also obligated (though not strictly required) to buy one of the support packages if MySQL is part of your mission-critical operations.

Since these costs are orders of magnitude less than commercial counterparts, there's no excuse for not contributing to the development effort through licensing and support packages.

Using MySQL with the Web

MySQL is an excellent general-purpose database, and includes native APIs that can be used to develop applications that incorporate native MySQL functionality. Web applications can certainly be written using one of these APIs, but can also be built using tools and APIs developed by others in the open-source community. While there are a number of combinations of tools, there are four major types of MySQL web database development:

The bottom line is that there is a way to use MySQL with virtually any major web application platform. While I can't recommend PHP enough as a companion to MySQL, I've also personally had good luck with ColdFusion and Perl.

Choosing YourSQL

MySQL is a solid, low-cost, high-performance platform for both building and deploying web database applications. It scales to fairly large and busy sites with ease and is suitable for a number of web application development tools.

It's not the right platform for a transaction-intensive financial system, nor is it the right system if you deal with terabytes of data. It may also not be the right system if you've got a few Oracle DBAs already on staff. But for projects ranging from personal hacking to dynamic news sites and search engines, it can provide all the power you need at the right price.

John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.

Discuss this article in the O'Reilly Network Forum.

Return to the O'Reilly Network Hub.

Copyright © 2009 O'Reilly Media, Inc.