24 How MySQL Compares to Other Databases

24.1 How MySQL Compares to mSQL

This section has been written by the MySQL developers, so it should be read with that in mind. But there are NO factual errors that we know of.

For a list of all supported limits, functions, and types, see the crash-me Web page.

Performance

For a true comparison of speed, consult the growing MySQL benchmark suite. See section 12.7 Using Your Own Benchmarks. Because there is no thread creation overhead, a small parser, few features, and simple security, mSQL should be quicker at:

• Tests that perform repeated connects and disconnects, running a very simple query during each connection.
• INSERT operations into very simple tables with few columns and keys.
• CREATE TABLE and DROP TABLE.
• SELECT on something that isn't an index. (A table scan is very easy.)

Because these operations are so simple, it is hard to be better at them when you have a higher startup overhead. After the connection is established, MySQL should perform much better. On the other hand, MySQL is much faster than mSQL (and most other SQL implementions) on the following:

• Complex SELECT operations.
• Retrieving large results (MySQL has a better, faster, and safer protocol).
• Tables with variable-length strings, because MySQL has more efficent handling and can have indexes on VARCHAR columns.
• Handling tables with many columns.
• Handling tables with large record lengths.
• SELECT with many expressions.
• SELECT on large tables.
• Handling many connections at the same time. MySQL is fully multi-threaded. Each connection has its own thread, which means that no thread has to wait for another (unless a thread is modifying a table another thread wants to access). In mSQL, once one connection is established, all others must wait until the first has finished, regardless of whether the connection is running a query that is short or long. When the first connection terminates, the next can be served, while all the others wait again, etc.
• Joins. mSQL can become pathologically slow if you change the order of tables in a SELECT. In the benchmark suite, a time more than 15000 times slower than MySQL was seen. This is due to mSQL's lack of a join optimizer to order tables in the optimal order. However, if you put the tables in exactly the right order in mSQL2 and the WHERE is simple and uses index columns, the join will be relatively fast! See section 12.7 Using Your Own Benchmarks.
• ORDER BY and GROUP BY.
• DISTINCT.
• Using TEXT or BLOB columns.

SQL Features

• GROUP BY and HAVING. mSQL does not support GROUP BY at all. MySQL supports a full GROUP BY with both HAVING and the following functions: COUNT(), AVG(), MIN(), MAX(), SUM(), and STD(). COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. MIN() and MAX() may take string arguments.
• INSERT and UPDATE with calculations. MySQL can do calculations in an INSERT or UPDATE. For example:

  mysql> UPDATE SET x=x*10+y WHERE x<20;
  

• Aliasing. MySQL has column aliasing.
• Qualifying column names. In MySQL, if a column name is unique among the tables used in a query, you do not have to use the full qualifier.
• SELECT with functions. MySQL has many functions (too many to list here; see section 7.4 Functions for Use in SELECT and WHERE Clauses).

Disk Space Efficiency

That is, how small can you make your tables? MySQL has very precise types, so you can create tables that take very little space. An example of a useful MySQL datatype is the MEDIUMINT that is 3 bytes long. If you have 100,000,000 records, saving even one byte per record is very important. mSQL2 has a more limited set of column types, so it is more difficult to get small tables.

Stability

This is harder to judge objectively. For a discussion of MySQL stability, see section 1.7 How Stable Is MySQL?. We have no experience with mSQL stability, so we cannot say anything about that.

Price

Another important issue is the license. MySQL has a more flexible license than mSQL, and is also less expensive than mSQL. Whichever product you choose to use, remember to at least consider paying for a license or e-mail support. (You are required to get a license if you include MySQL with a product that you sell, of course.)

Perl Interfaces

MySQL has basically the same interfaces to Perl as mSQL with some added features.

JDBC (Java)

MySQL currently has a lot of different JDBC drivers:

• The mm driver: A type 4 JDBC driver by Mark Matthews mmatthew@ecn.purdue.edu. This is released under the LGPL.
• The Resin driver. This is a commercial JDBC driver released under open source. http://www.caucho.com/projects/jdbc-mysql/index.xtp
• The gwe driver: A Java interface by GWE technologies (not supported anymore).
• The jms driver: An improved gwe driver by Xiaokun Kelvin ZHU X.Zhu@brad.ac.uk (not supported anymore).
• The twz driver: A type 4 JDBC driver by Terrence W. Zellers zellert@voicenet.com. This is commercial but is free for private and educational use (not supported anymore).

The recommended driver is the mm driver. The Resin driver may also be good (at least the benchmarks looks good), but we haven't received that much information about this yet. We know that mSQL has a JDBC driver, but we have too little experience with it to compare.

Rate of Development

MySQL has a very small team of developers, but we are quite used to coding C and C++ very rapidly. Because threads, functions, GROUP BY, and so on are still not implemented in mSQL, it has a lot of catching up to do. To get some perspective on this, you can view the mSQL `HISTORY' file for the last year and compare it with the News section of the MySQL Reference Manual (see section F MySQL change history). It should be pretty obvious which one has developed most rapidly.

Utility Programs

Both mSQL and MySQL have many interesting third-party tools. Because it is very easy to port upward (from mSQL to MySQL), almost all the interesting applications that are available for mSQL are also available for MySQL. MySQL comes with a simple msql2mysql program that fixes differences in spelling between mSQL and MySQL for the most-used C API functions. For example, it changes instances of msqlConnect() to mysql_connect(). Converting a client program from mSQL to MySQL usually takes a couple of minutes.

24.1.1 How to Convert mSQL Tools for MySQL

According to our experience, it would just take a few hours to convert tools such as msql-tcl and msqljava that use the mSQL C API so that they work with the MySQL C API.

The conversion procedure is:

1. Run the shell script msql2mysql on the source. This requires the replace program, which is distributed with MySQL.
2. Compile.
3. Fix all compiler errors.

Differences between the mSQL C API and the MySQL C API are:

• MySQL uses a MYSQL structure as a connection type (mSQL uses an int).
• mysql_connect() takes a pointer to a MYSQL structure as a parameter. It is easy to define one globally or to use malloc() to get one. mysql_connect() also takes two parameters for specifying the user and password. You may set these to NULL, NULL for default use.
• mysql_error() takes the MYSQL structure as a parameter. Just add the parameter to your old msql_error() code if you are porting old code.
• MySQL returns an error number and a text error message for all errors. mSQL returns only a text error message.
• Some incompatibilities exist as a result of MySQL supporting multiple connections to the server from the same process.

24.1.2 How mSQL and MySQL Client/Server Communications Protocols Differ

There are enough differences that it is impossible (or at least not easy) to support both.

The most significant ways in which the MySQL protocol differs from the mSQL protocol are listed below:

• A message buffer may contain many result rows.
• The message buffers are dynamically enlarged if the query or the result is bigger than the current buffer, up to a configurable server and client limit.
• All packets are numbered to catch duplicated or missing packets.
• All column values are sent in ASCII. The lengths of columns and rows are sent in packed binary coding (1, 2, or 3 bytes).
• MySQL can read in the result unbuffered (without having to store the full set in the client).
• If a single read/write takes more than 30 seconds, the server closes the connection.
• If a connection is idle for 8 hours, the server closes the connection.

24.1.3 How mSQL 2.0 SQL Syntax Differs from MySQL

Column types

MySQL

Has the following additional types (among others; see section 7.7 CREATE TABLE Syntax):

• ENUM type for one of a set of strings.
• SET type for many of a set of strings.
• BIGINT type for 64-bit integers.

MySQL also supports the following additional type attributes:

• UNSIGNED option for integer columns.
• ZEROFILL option for integer columns.
• AUTO_INCREMENT option for integer columns that are a PRIMARY KEY. See section 23.4.30 mysql_insert_id().
• DEFAULT value for all columns.

mSQL2

mSQL column types correspond to the MySQL types shown below:

mSQL type	Corresponding MySQL type
CHAR(len)	CHAR(len)
TEXT(len)	TEXT(len). len is the maximal length. And LIKE works.
INT	INT. With many more options!
REAL	REAL. Or FLOAT. Both 4- and 8-byte versions are available.
UINT	INT UNSIGNED
DATE	DATE. Uses ANSI SQL format rather than mSQL's own format.
TIME	TIME
MONEY	DECIMAL(12,2). A fixed-point value with two decimals.

Index Creation

MySQL

Indexes may be specified at table creation time with the CREATE TABLE statement.

mSQL

Indexes must be created after the table has been created, with separate CREATE INDEX statements.

To Insert a Unique Identifier into a Table

MySQL

Use AUTO_INCREMENT as a column type specifier. See section 23.4.30 mysql_insert_id().

mSQL

Create a SEQUENCE on a table and select the _seq column.

To Obtain a Unique Identifier for a Row

MySQL

Add a PRIMARY KEY or UNIQUE key to the table and use this. New in Version 3.23.11: If the PRIMARY or UNIQUE key consists of only one column and this is of type integer, one can also refer to it as _rowid.

mSQL

Use the _rowid column. Observe that _rowid may change over time depending on many factors.

To Get the Time a Column Was Last Modified

MySQL

Add a TIMESTAMP column to the table. This column is automatically set to the current date and time for INSERT or UPDATE statements if you don't give the column a value or if you give it a NULL value.

mSQL

Use the _timestamp column.

NULL Value Comparisons

MySQL

MySQL follows ANSI SQL, and a comparison with NULL is always NULL.

mSQL

In mSQL, NULL = NULL is TRUE. You must change =NULL to IS NULL and <>NULL to IS NOT NULL when porting old code from mSQL to MySQL.

String Comparisons

MySQL

Normally, string comparisons are performed in case-independent fashion with the sort order determined by the current character set (ISO-8859-1 Latin1 by default). If you don't like this, declare your columns with the BINARY attribute, which causes comparisons to be done according to the ASCII order used on the MySQL server host.

mSQL

All string comparisons are performed in case-sensitive fashion with sorting in ASCII order.

Case-insensitive Searching

MySQL

LIKE is a case-insensitive or case-sensitive operator, depending on the columns involved. If possible, MySQL uses indexes if the LIKE argument doesn't start with a wild-card character.

mSQL

Use CLIKE.

Handling of Trailing Spaces

MySQL

Strips all spaces at the end of CHAR and VARCHAR columns. Use a TEXT column if this behavior is not desired.

mSQL

Retains trailing space.

WHERE Clauses

MySQL

MySQL correctly prioritizes everything (AND is evaluated before OR). To get mSQL behavior in MySQL, use parentheses (as shown in an example below).

mSQL

Evaluates everything from left to right. This means that some logical calculations with more than three arguments cannot be expressed in any way. It also means you must change some queries when you upgrade to MySQL. You do this easily by adding parentheses. Suppose you have the following mSQL query:

mysql> SELECT * FROM table WHERE a=1 AND b=2 OR a=3 AND b=4;

To make MySQL evaluate this the way that mSQL would, you must add parentheses:

mysql> SELECT * FROM table WHERE (a=1 AND (b=2 OR (a=3 AND (b=4))));

Access Control

MySQL

Has tables to store grant (permission) options per user, host, and database. See section 6.9 How the Privilege System Works.

mSQL

Has a file `mSQL.acl' in which you can grant read/write privileges for users.

24.2 How MySQL Compares to PostgreSQL

We would first like to note that PostgreSQL and MySQL are both widely used products, but their design goals are completely different. This means that for some applications MySQL is more suitable and for others PostgreSQL is more suitable. When choosing which database to use, you should first check if the database's feature set is good enough to satisfy your application. If you need speed, MySQL is probably your best choice. If you need some of the extra features that PostgreSQL can offer, you should use PostgreSQL.

PostgreSQL has some more advanced features like user-defined types, triggers, rules, and some transaction support (currently it has about the same symantics as MySQL's transactions in that the transaction is not 100% atomic). However, PostgreSQL lacks many of the standard types and functions from ANSI SQL and ODBC. See the crash-me Web page (http://www.mysql.com/information/crash-me.php) for a complete list of limits and which types and functions are supported or unsupported.

Normally, PostgreSQL is a magnitude slower than MySQL. See section 12.7 Using Your Own Benchmarks. This is due largely to the fact that they have only transaction-safe tables and that their transactions system is not as sophisticated as Berkeley DB's. In MySQL you can decide per table if you want the table to be fast or take the speed penalty of making it transaction safe.

The most important things that PostgreSQL supports that MySQL doesn't yet support:

Sub select

Foreign keys

Stored procedures

An extendable type system.

A way to extend the SQL to handle new key types (like R-trees)

MySQL, on the other hand, supports many ANSI SQL constructs that PostgreSQL doesn't support. Most of these can be found at the crash-me Web page.

If you really need the rich type system PostgreSQL offers and you can afford the speed penalty of having to do everything transaction safe, you should take a look at PostgreSQL.

Go to the first, previous, next, last section, table of contents.