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


21 How MySQL compares to other databases

21.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 10.8 Using your own benchmarks. Because there is no thread creation overhead, a small parser, few features and simple security, mSQL should be quicker at: Since 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:
SQL Features
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.6 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 email 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 4 JDBC drivers: The recommended drivers are the twz or mm driver. Both are reported to work excellently. 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. Since 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 D 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. Since 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.

21.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:

21.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:

21.1.3 How mSQL 2.0 SQL syntax differs from MySQL

Column types

MySQL
Has the following additional types (among others; see see section 7.7 CREATE TABLE syntax):
MySQL also supports the following additional type attributes:
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.
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 20.4.29 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 3.23.11: If the PRIMARY/ 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 wildcard 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 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.8 How the privilege system works.
mSQL
Has a file `mSQL.acl' in which you can grant read/write privileges for users.

21.2 How MySQL compares to PostgreSQL

PostgreSQL has some more advanced features like user-defined types, triggers, rules and some transaction support. However, PostgreSQL lacks many of the standard types and functions from ANSI SQL and ODBC. See the crash-me web page 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 10.8 Using your own benchmarks. This is due largely to their transactions system. If you really need transactions or the rich type system PostgreSQL offers and you can afford the speed penalty, you should take a look at PostgreSQL.


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