Search the MySQL manual:

1.10.1 How MySQL Compares to mSQL

Performance
For a true comparison of speed, consult the growing MySQL benchmark suite. See section 5.1.4 The MySQL Benchmark Suite. 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 indexed. (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 Server should perform much better. On the other hand, MySQL Server is much faster than mSQL (and most other SQL implementations) on the following:
  • Complex SELECT operations.
  • Retrieving large results (MySQL Server has a better, faster, and safer protocol).
  • Tables with variable-length strings because MySQL Server 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 Server 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 15,000 times slower than MySQL Server was seen. This is due to mSQL's lack of a join optimiser 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 5.1.4 The MySQL Benchmark Suite.
  • 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 Server supports a full GROUP BY with both HAVING and the following functions: COUNT(), AVG(), MIN(), MAX(), SUM(), and STD(). COUNT(*) is optimised 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 Server can do calculations in an INSERT or UPDATE. For example: 
    mysql> UPDATE SET x=x*10+y WHERE x<20;
  • Aliasing. MySQL Server has column aliasing.
  • Qualifying column names. In MySQL Server, 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 Server has many functions (too many to list here; see section 6.3 Functions for Use in SELECT and WHERE Clauses).
Disk Space Efficiency
That is, how small can you make your tables? MySQL Server 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 million records, saving even 1 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 Server stability, see section 1.2.3 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 Server 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.
Perl Interfaces
MySQL Server has basically the same interfaces to Perl as mSQL with some added features.
JDBC (Java)
MySQL Server currently has a lot of different JDBC drivers:
  • MySQL Connector/J is a native Java driver. Version 3.x is released under dual licensing (GPL and commercial).
  • 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 (no longer supported).
  • The jms driver: an improved gwe driver by Xiaokun Kelvin ZHU X.Zhu@brad.ac.uk (no longer supported).
  • 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 (no longer supported).
The recommended driver is MySQL Connector/J. The Resin driver may also be good (at least the benchmarks look 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 Server has a small core 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 D MySQL Change History). It should be pretty obvious which one has developed most rapidly.
Utility Programs
Both mSQL and MySQL Server have many interesting third-party tools. Because it is very easy to port upward (from mSQL to MySQL Server), almost all the interesting applications that are available for mSQL are also available for MySQL Server. MySQL Server comes with a simple msql2mysql program that fixes differences in spelling between mSQL and MySQL Server 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 Server usually requires only minor effort.

Subsections

User Comments

Add your own comment.