How does MySQL compare with other databases

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 use the growing MySQL benchmarka 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.
• Handle 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).
• 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 use 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 has 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.

How about mSQL tools like msql-tcl, msqljava?

According to our experience it would just take a few hours to convert a tool using the mSQL C API to the MySQL C API.

The procedure:

1. Run the shell script msql2mysql on the source. This needs the binary program replace, which is distributed with MySQL.
2. Compile
3. Fix all compiler errors:

Differences between the MySQL and mSQL C API's.

• MySQL uses MySQL as a connection type (mSQL uses an int).
• MySQL connect takes a pointer to a MYSQL structure as a parameter. It is easy to define one globally or use malloc to get one.
• mysql_connect takes 2 extra parameters. You may set these to NULL,NULL for default use (user and password).
• 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.
• Some incompatibilities exist as a result of MySQL supporting multiple connections to the server from the same process.
• MySQL returns a error number and a text for all errors. mSQL only returns a text error

How different from mSQL are the MySQL client/server communications protocols?

There are enough differences that it is impossible (at least not easy) to support both.

The greatest differences between MySQL and mSQL are:

• A message buffer may contain many result rows.
• The message buffers are dynamically enlarged if the question or the result is bigger than the current buffer up to a configurable server and client limit.
• All packages are numbered to catch out dupplicated or missing packets.
• All columns are sent in ASCII, the length 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 then the server closes the connection.
• If nothing has happened on a connection for 8 hours the server closes the connection.

What are the differences in the SQL syntax between MySQL & mSQL 2.0?

• CREATE TABLE

  MySQL

  Has (among others) the following extra types: See section CREATE TABLE syntax.

  • UNSIGNED option for all integer columns.
  • ZEROFILL option for all integer columns.
  • AUTO_INCREMENT option for all integer columns that also is a PRIMARY KEY. See section How can I get the unique ID for the last inserted row?.
  • DEFAULT value for all columns.
  • ENUM type for one of a set of strings.
  • SET type for many of a set of string.
  • BIGINT type for 64 bit integers.

  mSQL2

  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 bytes versions are available.
  uint	Unsigned integer.
  date	date. Takes ANSI SQL format instead of mSQLs own.
  time	time
  money	decimal(12,2). A fixed point value with two decimals.

• To create indexes.

  MySQL

  All indexes have to be given to CREATE TABLE. Indexes can not be removed without recreating the table. See section ALTER TABLE syntax.

  mSQL

  Indexes must be created by a separate CREATE INDEX clause. Indexes may be removed with DROP INDEX.

• To get a unique identifier in a table at insert.

  MySQL

  Use AUTO_INCREMENT as a column type specifier. See section How can I get the unique ID for the last inserted row?.

  mSQL

  Create a SEQUENCE on a table and select the _seq column.

• To get a unique identifier for a row.

  MySQL

  Add a PRIMARY KEY to the table.

  mSQL

  Use the _rowid column. Observe that _rowid may change depending on many factors.

• To get the time a column was last modified.

  MySQL

  Add a TIMESTAMP column to the table. This column will automaticly be updated to the current time if you don't give the column a value or if you give it a NULL value.

  mSQL

  Use the _timestamp column.

• In mSQL NULL = NULL is TRUE. MySQL follows ANSI SQL and a comparison with NULL is always NULL. One has to change =NULL to IS NULL when porting old code from mSQL to MySQL.
• All string comparisons are by default are case independent with case according to ISO-8859-1 Latin1. If you don't like this you should declare your strings with the BINARY attribute.
• Case insensitive searching.

  MySQL

  LIKE is always case insensitive. Also if possible MySQL uses indexes if the like argument doesn't start with a wildcard.

  mSQL

  Use CLIKE.

• End space handling.

  MySQL

  Stripps all spaces att the end of CHAR and VARCHAR columns. Currently use a TEXT column if this behavior is undesired.

  mSQL

  Keeps end space.

• What are the differences in the WHERE statement?

  MySQL

  MySQL does everything correctly prioritized (AND is evaluated before OR). To get mSQL behaviour in MySQL, use parenthesis: select * from table where a=1 and b=2 or a=3 and b=4 -> select * from table where (a=1 and (b=2 or (a=3 and (b=4)))).

  mSQL

  Evaluates everything from left to right. That means that some logical calculation with more that 3 arguments can not be expressed in any way. This also mean that you have to change some queries when you upgrade to MySQL. You do this easy by adding parenthesis. Always when you are in doubt about what a query does:

  1. Remove all distinct keywords to make the query easier to debug.
  2. Write all columns from all tables to really see what you are doing.

  This will make it easier to see what goes wrong.

• Access Control

  MySQL

  Has tables to store grant options per user, host and db. See section How does the privilege system work?

  mSQL

  Has a file `mSQL.acl' where you can grant read/write privileges for users.

How does MySQL compare with PostgreSQL

For a list of all supported limits, functions and types see the crash-me web page.

PostgreSQL has some more advanced features like user-defined types, triggers, rules and transactions. But it lacks a lot of the standard types and functions from ANSI SQL and ODBC. See the crash-me web page for a complete list of supported/unsupported types/function.

Normally PostgreSQL is much slower than MySQL. See section MySQL benchmark suite.

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