How does MySQL compare with 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 see the growing MySQL benchmark suite. See section MySQL benchmark suite MySQL is significantly quicker on complex selects. mSQL can get patalogically slow if you chage the order of tables in a select. In the benchmark suite a time more that 15000 times slower than MySQL was seen. Because of no thread creation overhead, small parser, few features and simple security mSQL should be quicker at:

Test that do a lot of connect/disconnect with a very simple query. Inserts into very simple tables with few columns and keys.
• CREATE TABLE and DROP TABLE.
• SELECT one something that isn't an index. (A table scan is very easy)

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

Retrieving large results (MySQL has a better, faster and safer protocol)
• Tables with variable length strings since MySQL has more efficent handling and can have indexes on varchars.
• Handling tables with many columns.
• Handling tables with large record lengths.
• SELECT with many expressions.
• SELECT on large tables.
• Handles many connections at the same time. MySQL is fully multi-threaded. Each connection has it is own thread, which means that none of them has to wait for the other (unless a query modifying the same table you need to use is executed). In mSQL all other connections have to wait until the first, doesn't matter whether the query is long or short, is executed and finished. After that the next connection can be served, while all the others wait again, etc.
• Joins (We have seen (See section MySQL benchmark suite) speed differences of 17000 times on this one). This is because mSQL doesn't have a join optimizer that orders tables in the optimal order. If one however put the tables in exactly the right order in mSQL 2 the join will be relatively fast!
• ORDER BY and GROUP BY.
• DISTINCT.
• Using TEXT or BLOB columns.

SQL Features

1. GROUP BY & HAVING MySQL supports a full GROUP BY with both HAVING and the following functions count(), avg(), min(), max(), sum() and std(). min() and max() may take string arguments. count(*) is optimised to return very quickly if this is the only thing in the query. mSQL does not support GROUP BY at all.
2. INSERT & UPDATE with calculations. MySQL can do calculations in a INSERT or UPDATE.

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

3. Aliasing MySQL has column aliasing.
4. Qualifying column names. If a column name is unique you do not have to use the full qualifier.
5. SELECT with functions MySQL has too many functions to list here. See section 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 that use 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 10.000.0000 records even saving one byte per record is very important. Since mSQL2 only has 4 types (char,text,int,real) it is hard to get small tables.

Stability

This is harder to judge objectively. For MySQL stability see See section How stable is MySQL?. We have no experience with mSQL stability so we can not say anything about that.

Price

Another important issue is of course the license. MySQL has a more flexible license than mSQL and is also cheaper than mSQL. Remember to at least consider paying for a license or email support for whatever product you choose to use. If you sell a product with MySQL you are of course required to get a license for this.

Perl interfaces

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

JDBC (Java)

MySQL has a Java interface by GWE technologies that has been improved by Xiaokun Kelvin ZHU. We know that mSQL has one 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. See section MySQL change history. It should be pretty obvious which one has developed most rapidly.

Utility programs

Both mSQL and MySQL have a lot of interesting third-party tools. Since it is very easy to port upwars (mSQL -> MySQL) MySQL has almost all interesting mSQL applications. MySQL comes with a simple msql2mysql program that fixes the different spelling of the most used functions. A conversion of a client program from mSQL to mySQL usually takes a couple of minutes.

• Using mSQL tools: How about mSQL tools like msql-tcl, msqljava?
• Protocol differences: How different from mSQL are the MySQL client/server communications protocols?
• Syntax differences: What are the differences in the SQL syntax between MySQL & mSQL 2.0?

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