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.
mSQLshould be quicker at:
INSERToperations into very simple tables with few columns and keys.
SELECTon something that isn't an index. (A table scan is very easy.)
mSQL(and most other SQL implementions) on the following:
SELECTwith many expressions.
SELECTon large tables.
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.
mSQLcan 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
mSQL2 and the
WHEREis simple and uses index columns, the join will be relatively fast! See section 11 The MySQL benchmark suite.
mSQLdoes not support
GROUP BYat all. MySQL supports a full
GROUP BYwith both
HAVINGand the following functions:
COUNT(*)is optimized to return very quickly if the
SELECTretrieves from one table, no other columns are retrieved and there is no
MAX()may take string arguments.
UPDATEwith calculations. MySQL can do calculations in an
UPDATE. For example:
mysql> UPDATE SET x=x*10+y WHERE x<20;
SELECTwith functions. MySQL has many functions (too many to list here; see section 7.3 Functions for use in
MEDIUMINTthat is 3 bytes long. If you have 100,000,000 records, saving even one byte per record is very important.
mSQL2has a more limited set of column types, so it is more difficult to get small tables.
mSQLstability, so we cannot say anything about that.
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, of course.)
mSQLwith some added features.
mSQLhas a JDBC driver, but we have too little experience with it to compare.
GROUP BYand 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.
mSQLand MySQL have many interesting third-party tools. Since it is very easy to port upward (from
mSQLto MySQL), almost all the interesting applications that are available for
mSQLare also available for MySQL. MySQL comes with a simple
msql2mysqlprogram that fixes differences in spelling between
mSQLand MySQL for the most-used C API functions. For example, it changes instances of
mysql_connect(). Converting a client program from
mSQLto MySQL usually takes a couple of minutes.
mSQLtools for MySQL
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:
msql2mysqlon the source. This requires the
replaceprogram, which is distributed with MySQL.
Differences between the
mSQL C API and the MySQL C API are:
MYSQLstructure as a connection type (
mysql_connect()takes a pointer to a
MYSQLstructure as a parameter. It is easy to define one globally or to use
malloc()to get one.
mysql_connect()also takes 2 parameters for specifying the user and password. You may set these to
NULL, NULLfor default use.
MYSQLstructure as a parameter. Just add the parameter to your old
msql_error()code if you are porting old code.
mSQLreturns only a text error message.
mSQLand MySQL client/server communications protocols differ
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:
mSQL2.0 SQL syntax differs from MySQL
ENUMtype for one of a set of strings.
SETtype for many of a set of strings.
BIGINTtype for 64-bit integers.
UNSIGNEDoption for integer columns.
ZEROFILLoption for integer columns.
AUTO_INCREMENToption for integer columns that are a
PRIMARY KEY. See section 18.4.27
DEFAULTvalue for all columns.
mSQLcolumn types correspond to the MySQL types shown below:
|Corresponding MySQL type|
To insert a unique identifier into a table
AUTO_INCREMENTas a column type specifier. See section 18.4.27
SEQUENCEon a table and select the
To obtain a unique identifier for a row
UNIQUEkey to the table.
_rowidcolumn. Observe that
_rowidmay change over time depending on many factors.
To get the time a column was last modified
TIMESTAMPcolumn to the table. This column is automatically set to the current date and time for
UPDATEstatements if you don't give the column a value or if you give it a
NULL value comparisons
NULL = NULLis TRUE. You must change
IS NOT NULLwhen porting old code from
BINARYattribute, which causes comparisons to be done according to the ASCII order used on the MySQL server host.
LIKEis a case-insensitive or case-sensitive operator, depending on the columns involved. If possible, MySQL uses indexes if the
LIKEargument doesn't start with a wildcard character.
Handling of trailing spaces
VARCHARcolumns. Use a
TEXTcolumn if this behavior is not desired.
ANDis evaluated before
OR). To get
mSQLbehavior in MySQL, use parentheses (as shown below).
mysql> SELECT * FROM table WHERE a=1 AND b=2 OR a=3 AND b=4;To make MySQL evaluate this the way that
mSQLwould, you must add parentheses:
mysql> SELECT * FROM table WHERE (a=1 AND (b=2 OR (a=3 AND (b=4))));
PostgreSQL has some more advanced features like user-defined
types, triggers, rules and transactions. But it lacks many of the
standard types and functions from ANSI SQL and ODBC. See the
crash-me web page
for a complete list of which limits, types and functions are supported
PostgreSQL is much slower than
MySQL. See section 11 The MySQL benchmark suite. This is due largely to their
transactions system. If you really need transactions and can afford to
pay the speed penalty, you should take a look at PostgreSQL.
Go to the first, previous, next, last section, table of contents.