MySQL and mSQL

MySQL and mSQL are very similar, cheap, lightweight, and fast databases. This book covers both databases due to their overwhelming similarity. They are, however, different in very important ways and we will be sure to cover those differences as well. Both systems support C, Perl, Java (via Java DataBase Connectivity API [JDBC]), and Python programming. With the tools MySQL and mSQL provide these languages, it is possible to create full-blown client/server applications and database-integrated web sites and not spend a fortune. This is great news for the small web publisher or for anyone developing small-scale client/server applications who cannot afford to purchase one of the commercially available products.

The inexpensive—in some cases, free—nature of MySQL and mSQL does not come cost free. Neither DBMS supports the full range of SQL. These engines lack some features that may be required by more complex applications. For some applications you also have to work a little harder on the client side to meet needs that you get for free from expensive database engines. We will, however, teach you how to build portable MySQL and mSQL applications so that you have the option to try out more heavy-weight database engines when your needs demand them—you won’t have to make a ton of changes to migrate to a big-time database. In order to understand what these two engines have to offer, it is best to take a brief look at their histories.

The History of mSQL

Before 1994, you were out of luck if you wanted a SQL-based RDBMS without paying large sums of money. The dominant commercial SQL solutions were Oracle, Sybase, and Informix. These database engines were designed to handle tremendous amounts of data with very complex relationships. They were powerful and full of features—and very resource intensive and expensive. In those days, you could not buy a $2,000 200 MHz Pentium server. The resources required by these database engines cost tens of thousands of dollars.

Large corporations and major universities had no problem spending millions of dollars per year on these large DBMS/server combinations. As a small organization or individual user, however, you had to settle for weak desktop database programs. A few cheap client/server database engines did exist at that time, but none of them used SQL as their query language. The most notable of these database engines was Postgres, which was a DBMS descended from the same roots as the commercial Ingres RDBMS. Postgres, unfortunately, came with similar resource requirements as its commercial counterparts without the advantage of SQL as a query language. At the time, postgres used a variant of the QUEL language called PostQUEL.

David Hughes

As part of his Ph.D. thesis at Bond University in Australia, David Hughes (a.k.a. Bambi) was developing a system of monitoring and managing a variety of systems from one or more locations. This project was called the Minerva Network Management System. A key piece of Minerva was a DBMS for storing information about the machines on the network. As a university student without direct access to a server running one of the major commercial relational database engines, Hughes looked to Postgres as the obvious solution to his database needs.

Hughes’ colleagues initially suggested that SQL should be the standard query language for Minerva. After all, SQL was—and still is—the most overwhelmingly accepted standard for a query language. By standardizing on SQL, Minerva could serve the needs of people all over the world just as long as they had some type of SQL DBMS installed. In other words, SQL exposed Minerva to a much wider audience than PostQUEL, which was limited to Postgres. As it turns out, today even Postgres speaks SQL.

The tug-of-war between the SQL standard and access to a SQL database engine left Hughes in a bind. If he based Minerva’s query language on SQL, he would have no database engine. Because buying a multithousand dollar RDBMS was not an option, Hughes took a creative approach to the problem. He decided the solution was to create an application that could translate SQL into PostQUEL on the fly. This program would intercept all SQL statements sent from Minerva, convert them to PostQUEL, and then send the PostQUEL on to Postgres. Hughes created this product and named it miniSQL, or mSQL.

From PostQUEL translator to RDBMS

For a while, this configuration worked well for Hughes’ needs. The Minerva system did not care what DBMS was in use so long as it understood SQL. As far as Minerva knew, Postgres did understand SQL because mSQL was there in the middle to handle PostQUEL translation. Unfortunately, as Minerva grew bigger, it also grew significantly slower. It eventually became clear that Postgres—or any other huge RDBMS—was not capable of supporting the small feature set demanded by Minerva in the limited resources available to Minerva. For example, Minerva required multiple simultaneous database connections. In order to support this, Postgres required multiple instances^[2] of the database server to be running at the same time. In addition, several potential contributors to the Minerva project could not get involved because Postgres did not support their systems and they, too, did not have the option of purchasing an expensive SQL-based DBMS.

In the face of these problems, Hughes reevaluated his decision to use Postgres. As large and complex as it was, it was likely too complex for Minerva’s needs. Most of Minerva’s queries were simple INSERT, DELETE, and SELECT statements. All of the other stuff that cost Postgres in terms of performance simply was not required by Minerva.

Hughes already had mSQL doing SQL translation. He only needed to add data storage and retrieval capabilities to it and he had a database server that met his needs. This evolution led to the mSQL to that exists today.

The History of MySQL

It would be a mistake to characterize MySQL as a simple reaction to mSQL’s failures. Its inventor, Michael Widenius (a.k.a. Monty) at the Swedish company TcX, has been working with databases since 1979. Until recently, Widenius was the only developer at TcX. In 1979, he developed an in-house database tool called UNIREG for managing databases. Since 1979, UNIREG has been rewritten in several different languages and extended to handle big databases.

In 1994, TcX began developing web-based applications and used UNIREG to support this effort. Unfortunately, UNIREG created too much overhead to be successful in dynamically generating web pages. TcX began looking at SQL and mSQL. At that time, however, mSQL was still in its 1.x releases. As we mentioned, mSQL 1.x did not support any indices. mSQL’s performance was therefore poor in comparison to UNIREG.

Widenius contacted David Hughes—the author of mSQL—to see if Hughes would be interested in connecting mSQL to UNIREG’s B+ ISAM handler. Hughes was already well on his way to mSQL 2, however, and already had his indexing infrastructure in place. TcX decided to create a database server that was more compatible with its requirements.

TcX was smart enough not to try to reinvent the wheel. It built upon UNIREG and capitalized on the growing number of third party mSQL utilities by writing an API into its system that was, at least initially, practically identical to the mSQL API. As a result, an mSQL user who wanted to move to the TcX more feature-rich database server would only have to make trivial changes to any existing code. The code supporting this new database, however, was completely original.

By May 1995, TcX had a database that met its internal needs—MySQL 1.0. A business partner, David Axmark at Detron HB, began pressing TcX to release this server on the Internet. The goal of an Internet release would be to use a business model pioneered by Aladdin Peter Deutsch. The result was a very flexible copyright that makes MySQL “more free” than mSQL.

As for the name MySQL, Widenius says, “It is not perfectly clear where the name MySQL derives from. TcX’s base directory and a large amount of their libraries and tools have had the prefix ‘my’ for well over 10 years. However, my daughter (some years younger) is also named My. So which of the two gave its name to MySQL is still a mystery.”

Since the initial Internet release of MySQL, it has been ported to a host of Unix operating systems, Win32, and OS/2. TCX estimates that MySQL runs on about 500,000 severs.

Major changes in Version 3.22, the current recommended version, are:

• Better security

• Faster connections, faster parsing of SQL queries, and a better query optimizer

• Support for more operating systems

• INSERT DELAYED

• GRANT and REVOKE commands

• CREATE INDEX and DROP INDEX

• HIGH_PRIORITY and LOW_PRIORITY lock levels for SELECT, INSERT, UPDATE, and DELETE statements

• A new FLUSH command operating on TABLES, HOSTS, LOGS, and PRIVILEGES

• A new KILL command in SQL that works like kill under Unix or msqladmin

• A HAVING clause supporting expressions

• Compressed client/server protocol

• Saving default program options in my.cnf files

Major changes in Version 3.23, a development version, are:

• Tables directly portable between different operating systems and CPUs

• Temporary tables and HEAP tables, which are stored only in RAM

• Support for big files (63 bit) on operating systems that support them

• True floating point fields

• Comments on tables

• Sample ANALYSE( ) procedure

• User-defined functions

• Much faster SELECT DISTINCT handling

• COUNT(DISTINCT)

Future enhancements planned for 3.23 include support for nesting one SELECT statement inside another and support for replicating databases, which permits load distribution among multiple servers and recovery in case of hardware failure.

MySQL is a very rapidly evolving database platform because of the army of volunteer coders who are helping to add to its strong base. You should therefore not be too surprised to find something that was true when we wrote this chapter is no longer true!

MySQL or mSQL?

We certainly have not yet provided you with enough information from which to make a decision. To get a full appreciation for the differences between the two engines as they exist today, you need to read on and understand the nuances as we present them in this book. On the surface, MySQL appears to be the obvious choice. mSQL fell behind after a time and is currently slower. David Hughes is not complacent. He is working on Version 2.1 of mSQL which should address many of its current shortcomings. At the same time, however, MySQL is moving ahead at the speed of light.

The case for mSQL may depend on the tools you are using. Because mSQL has been around longer, you may find more luck locating a tool that supports your specific needs. For example, only mSQL had a JDBC 2.0 compliant JDBC driver for Java database access at the time of this book’s publication. Certainly this situation will have changed by the time you read this book. Nevertheless, you need to consider issues such as that when you decide which database to use.

No matter which database you use, you will be a winner. Both database engines are faster than any other choice you will make. Both database engines are perfect for mid-range database needs. For an objective comparison of these two databases with each other or any other database, we recommend you visit http://www.mysql.com/crash-me-choose.htmy. It is on the MySQL home page, but its criteria are openly verifiable and it is very well done.

---

^[2] Each process running the same program is called an instance of that program, because it occupies memory just as an instance of a variable takes up a program’s memeory.