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 12.7 Using Your Own Benchmarks.
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.4 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 e-mail 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 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 F MySQL change history). It should be pretty obvious which one has developed most rapidly.
mSQLand MySQL have many interesting third-party tools. Because 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 two 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 23.4.30
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 23.4.30
SEQUENCEon a table and select the
To Obtain a Unique Identifier for a Row
UNIQUEkey to the table and use this. New in Version 3.23.11: If the
UNIQUEkey consists of only one column and this is of type integer, one can also refer to it as
_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 wild-card 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 in an example 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))));
We would first like to note that
PostgreSQL and MySQL
are both widely used products, but their design goals are completely
different. This means that for some applications MySQL is more
suitable and for others
PostgreSQL is more suitable. When
choosing which database to use, you should first check if the database's
feature set is good enough to satisfy your application. If you need
speed, MySQL is probably your best choice. If you need some
of the extra features that
PostgreSQL can offer, you should use
PostgreSQL has some more advanced features like user-defined
types, triggers, rules, and some transaction support (currently it
has about the same symantics as MySQL's transactions in that the
transaction is not 100% atomic). However, PostgreSQL lacks many of the
standard types and functions from ANSI SQL and ODBC. See the
Web page (http://www.mysql.com/information/crash-me.php) for a complete
list of limits and which types and functions are supported or unsupported.
PostgreSQL is a magnitude slower than MySQL.
See section 12.7 Using Your Own Benchmarks. This is due largely to the fact that they have only
transaction-safe tables and that their transactions system is not as
sophisticated as Berkeley DB's. In MySQL you can decide per
table if you want the table to be fast or take the speed penalty of
making it transaction safe.
The most important things that
PostgreSQL supports that MySQL
doesn't yet support:
An extendable type system.
A way to extend the SQL to handle new key types (like R-trees)
MySQL, on the other hand, supports many ANSI SQL constructs
PostgreSQL doesn't support. Most of these can be found at the
crash-me Web page.
If you really need the rich type system
PostgreSQL offers and you
can afford the speed penalty of having to do everything transaction
safe, you should take a look at
Go to the first, previous, next, last section, table of contents.