GNU Info
(mysql.info)Compare mSQL
How MySQL Compares to `mSQL'
----------------------------
*Performance*
For a true comparison of speed, consult the growing MySQL benchmark
suite. Note: MySQL 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 implementations) 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
efficient 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 `mSQL'2 and the `WHERE' is simple
and uses index columns, the join will be relatively fast!
Note: MySQL 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 Note: Functions).
*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 Note: Stability.
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 (Note:
News). 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.
Using mSQL tools- How to convert `mSQL' tools for MySQL
- Protocol differences
- How `mSQL' and MySQL Client/Server Communications Protocols Differ
- Syntax differences
- How `mSQL' 2.0 SQL Syntax Differs from MySQL
automatically generated by info2www version 1.2.2.9