Day 18: How MySQL Compares

Overview

There has been a lot of discussion about open source software. A lot of people do not place value on a
product because it is freely distributed. They do not feel that something that is open source can compete with
the established companies. Most people are more comfortable with a well-known name, even if that
company produces poor software. Linux has started to change that attitude. It has legitimized open source
software and has opened the door for products like MySQL. In this chapter, MySQL will be compared with
other databases in the same class. The strengths and weakness of MySQL will be discussed.

Today you will learn about

ƒ The speed factor

ƒ MySQL and stored procedures and workarounds

ƒ MySQL and transaction-based databases

ƒ MySQL and Constraints

The Need For Speed

As the need to store and access larger amounts of data grew, so did the need for databases engines to
retrieve and process requests quickly and efficiently. The race for the fastest database engine was on. At the
same time, the need for more functionality grew. DBAs needed more tools and more efficient ways to
manage the large amounts of data that they were storing. The problem was that the more functionality or
features that were added to a database, the slower the database became. MySQL has taken great measures
to ensure it provides the maximum number of features without sacrificing speed.
How fast is MySQL? TcX has set up a Web site (www.tcx.com) that displays the differences between
MySQL and other databases in its class. The Web site uses the results from a program called
crashme. This program lists and compares the functionality of all the major relational database
systems. TcX also has a link that displays the speed results of MySQL and other databases on several
different platforms using a common interface.
In almost every category, MySQL is the fastest. In a comparison of MySQL with DB2, Informix, Sybase
Enterprise 11.5, and Microsoft SQL Server all running on Windows NT and using ODBC as the common
interface, MySQL outperforms all the other databases in nearly every test. For example, in a test using
20,000 SELECT statements of various complexity, MySQL accomplished these tasks nearly 50% faster
than Microsoft SQL Server and nearly five times faster than Informix. According to the statistics
provided, MySQL outperforms all database engines in it class by an average of 40%.

As you know, statistics can be manipulated in many ways to illustrate a point. The best way to judge

MySQL is to use it. If you have ever used one of the big-name databases, you will be thoroughly

impressed by what MySQL can do. If you haven't had the opportunity, you will be spoiled by the speed

MySQL provides.

This is the reason why MySQL excels on the Internet. MySQL exceeds the speed requirements for the

Web. Queries are executed faster and results are returned quicker. Because it works so well across the

Internet, it makes a lot of sense to include MySQL in Enterprise applications as well. Imagine the

flexibility MySQL could provide with its large table size and unprecedented speed.

As explained earlier, with an increase in speed comes a decrease in functionality. The developers of

MySQL have done an excellent job of keeping the features-to-speed ratio relatively high. Of course they

couldn't include all the features that are available in other database engines in its class. So they have

selectively kept the essential features and let the optional features go by the wayside. In the near future,

MySQL will include many of the features that other databases have and still retain most of its speed.

Developers will let database administrators decide whether they want to use the more advanced feature

set and sacrifice speed. Giving the administrator the option is a huge benefit; it will put the decision in

the hands of the administrator instead of the manufacturer. That way, the people who really know what

they need can make the decision, instead of people who think they know what their users need.