Go to the first, previous, next, last section, table of contents.
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.
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
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
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
SELECT on something that isn't an index. (A table scan is very
most other SQL implementions) on the following:
Retrieving large results (MySQL has a better, faster and safer
Tables with variable-length strings, since MySQL has more efficent
handling and can have indexes on
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
mSQL can become pathologically slow if you change the order of tables
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
simple and uses index columns, the join will be relatively fast!
See section 10.8 Using your own benchmarks.
ORDER BY and
- 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.
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.
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,
- Perl interfaces
MySQL has basically the same interfaces to Perl as
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
We know that
The gwe driver: A Java interface by GWE technologies (not supported anymore).
The jms driver: An improved gwe driver by Xiaokun Kelvin ZHU
The twz driver: A type 4 JDBC driver by Terrence W. Zellers
and educational use.
The mm driver: A type 4 JDBC driver by Mark Matthews
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
`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
mSQL and MySQL have many interesting third-party
tools. Since it is very easy to port upward (from
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
mysql_connect(). Converting a client program from
MySQL usually takes a couple of minutes.
According to our experience, it would just take a few hours to convert tools
msqljava that use the
mSQL C API so that they work with the MySQL C API.
The conversion procedure is:
Run the shell script
msql2mysql on the source. This requires the
replace program, which is distributed with MySQL.
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 (
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
mysql_connect() also takes 2 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
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.
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
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
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 write/read takes more than 30 seconds, the server closes
If a connection is idle for 8 hours, the server closes the connection.
Has the following additional types (among others; see
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
See section 20.4.29
DEFAULT value for all columns.
mSQL column types correspond to the MySQL types shown below:
| Corresponding MySQL type
len is the maximal length.
INT. With many more options!
FLOAT. Both 4- and 8-byte versions are available.
DATE. Uses ANSI SQL format rather than
DECIMAL(12,2). A fixed-point value with two decimals.
Indexes may be specified at table creation time with the
Indexes must be created after the table has been created, with separate
CREATE INDEX statements.
To insert a unique identifier into a table
AUTO_INCREMENT as a column type
See section 20.4.29
SEQUENCE on a table and select the
To obtain a unique identifier for a row
PRIMARY KEY or
UNIQUE key to the table.
_rowid column. Observe that
_rowid may change over time
depending on many factors.
To get the time a column was last modified
TIMESTAMP column to the table. This column is automatically set
to the current date and time for
UPDATE statements if
you don't give the column a value or if you give it a
NULL value comparisons
ANSI SQL and a comparison with
NULL is always
NULL = NULL is TRUE. You
IS NULL and
IS NOT NULL when porting old code from
mSQL to 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.
All string comparisons are performed in case-sensitive fashion with
sorting in ASCII order.
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.
Handling of trailing spaces
Strips all spaces at the end of
columns. Use a
TEXT column if this behavior is not desired.
Retains trailing space.
MySQL correctly prioritizes everything (
AND is evaluated
OR). To get
mSQL behavior in MySQL, use
parentheses (as shown below).
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
mysql> SELECT * FROM table WHERE a=1 AND b=2 OR a=3 AND b=4;
To make MySQL evaluate this the way that
you must add parentheses:
mysql> SELECT * FROM table WHERE (a=1 AND (b=2 OR (a=3 AND (b=4))));
Has tables to store grant (permission) options per user, host and
database. See section 6.8 How the privilege system works.
Has a file `mSQL.acl' in which you can grant read/write privileges for
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
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.