This chapter compares MySQL to other popular databases.
This chapter has been written by the MySQL developers, so it should be read with that in mind. There are no factual errors contained in this chapter that we know of. If you find something which you believe to be an error, please contact us about it at firstname.lastname@example.org.
For a list of all supported limits, functions, and types, see the
crash-me Web page at
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 implementations) 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 13.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 126.96.36.199
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 188.8.131.52
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))));
When reading the following, please note that both products are continually evolving. We at MySQL AB and the PostgreSQL developers are both working on making our respective database as good as possible, so we are both a serious choice to any commercial database.
The following comparison is made by us at MySQL AB. We have tried to be as accurate and fair as possible, but because we don't have a full knowledge of all PostgreSQL features while we know MySQL througly, we may have got some things wrong. We will however correct these when they come to our attention.
We would first like to note that
PostgreSQL and MySQL
are both widely used products, but with different design goals, even if
we are both striving to be ANSI SQL compatible. 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 satisfies your
application. If you need speed, MySQL is probably your best
choice. If you need some of the extra features that only
can offer, you should use
When adding things to MySQL we take pride to do an optimal, definite solution. The code should be so good that we shouldn't have any need to change it in the foreseeable future. We also do not like to sacrifice speed for features but instead will do our utmost to find a solution that will give maximal throughput. This means that development will take a little longer, but the end result will be well worth this. This kind of development is only possible because all server code are checked by one of a few (currently two) persons before it's included in the MySQL server.
We at MySQL AB believe in frequent releases to be able to push out new features quickly to our users. Because of this we do a new small release about every 3 weeks, which a major branch every year. All releases are throughly tested with our testing tools on a lot of different platforms.
PostgreSQL is based on a kernel with lots of contributors. In this setup it makes sense to prioritize adding a lot of new features, instead of implementing them optimally, because one can always optimize things later if there arises a need for this.
Another big difference between MySQL and PostgreSQL is that nearly all of the code in the MySQL server are coded by developers that are employed by MySQL AB and are still working on the server code. The exceptions are the transaction engines and the regexp library.
This is in sharp contrast to the PostgreSQL code where the majority of the code is coded by a big group of people with different backgrounds. It was only recently that the PostgreSQL developers announced that they current developer group had finally had time to take a look at all the code in the current PostgreSQL release.
Both of the above development methods has it's own merits and drawbacks. We here at MySQL AB think of course that our model is better because our model gives better code consistence, more optimal and reusable code and, in our opinion, fewer bugs. Because we are the authors of the MySQL server code we are better able to coordinate new features and releases.
On the crash-me page you can find a list of those database constructs and limits that one can detect automatically with a program. Note however that a lot of the numerical limits may be changed with startup options for respective database. The above web page is however extremely useful when you want to ensure that your applications works with many different databases or when you want to convert your application from one datbase to another.
MySQL offers the following advantages over PostgreSQL:
MySQLis generally much faster than PostgreSQL. See section 25.2.3 Benchmarking MySQL and PostgreSQL.
MySQL. PostgreSQL doesn't yet support 24/7 systems because you have have to run
vacuum()once in a while to reclaim space from
DELETEcommands and to perform statistics analyzes that are critical to get good performance with PostgreSQL. Vacuum is also needed after adding a lot of new rows to a table. On a busy system with lots of changes vacuum must be run very frequently, in the worst cases even many times a day. During the
vacuum()run, which may take hours if the database is big, the database is from a production standpoint practically dead. The PostgreSQL team has fixing this on their TODO, but we assume that this is not an easy thing to fix permanently.
HEAPtables or disk based
MyISAM. See section 8 MySQL Table Types.
InnoDB). Because every transaction engine performs differently under different conditions, this gives the application writer more options to find an optimal solution for his/her setup. See section 8 MySQL Table Types.
MERGEtables gives you a unique way to instantly make a view over a set of identical tables and use these as one. This is perfectly for systems where you have log files that you order for example by month. See section 8.2 MERGE Tables.
update/deletegrants per user on a database or a table MySQL allows you to define a full set of different privileges on database, table and columns level. MySQL also allows you to specify the privilege on host+user combinations. See section 7.35
MySQLtable types (except InnoDB) are implemented as files (ie: one table per file), which makes it really easy to backup, move, delete and even symlink databases and tables when the server is down.
Drawbacks with MySQL compared to PostgreSQL:
MyISAMtables, is in many cases faster than page locks, row locks or versioning. The drawback however is that if one doesn't take into account how table locks works, a single long-running query can block a table for updates for a long time. This can usable be avoided when designing the application. If not, one can always switch the trouble table to use one of the transactional table types. See section 13.2.10 Table Locking Issues.
UPDATEand in MySQL 4.1 with
PostgreSQL offers currently the following advantages over MySQL:
Note that because we know the MySQL road map, we have included in the following table the version when MySQL should support this feature. Unfortunately we couldn't do this for previous comparison, because we don't know the PostgreSQL roadmap.
|Foreign keys||4.0 and 4.1|
|Stored procedures in multiple languages||4.1|
|Extensible type system.||Not planed|
|Full join.||4.0 or 4.1.|
|Cursors||4.1 or 4.2|
|Extensible index types like R-trees||R-trees are planned to 4.2|
|Inherited tables||Not planned|
Other reasons to use PostgreSQL:
Drawbacks with PostgreSQL compared to MySQL:
Vaccum()makes PostgreSQL hard to use in a 24/7 environment.
For a complete list of drawbacks, you should also examine the first table in this section.
The only open source benchmark, that we know of, that can be used to benchmark MySQL and PostgreSQL (and other databases) is our own. It can be found at: http://www.mysql.com/information/benchmarks.html.
We have many times asked the PostgreSQL developers and some PostgreSQL users to help us extend this benchmark to make the definitive benchmark for databases, but unfortunately we haven't got any feedback for this.
We, the MySQL developers, have because of this spent a lot of hours to get maximum performance from PostgreSQL for the benchmarks, but because we don't know PostgreSQL intimately we are sure that there are things that we have missed. We have on the benchmark page documented exactly how we did run the benchmark so that it should be easy for anyone to repeat and verify our results.
The benchmarks are usually run with and without the
option. When run with
--fast we are trying to use every trick
the server can do to get the code to execute as fast as possible.
The idea is that the normal run should show how the server would work in
a default setup and the
--fast run shows how the server would do
if the application developer would use extensions in the server to make
his application run faster.
When running with PostgreSQL and
--fast we do a
between after every major table update and drop table to make the database
in perfect shape for the following selects. The time for vacuum() is
When running with PostgreSQL 7.1.1 we could however not run with
--fast because during the insert test, the postmaster (the
PostgreSQL deamon) died and the database was so corrupted that it was
impossible to restart postmaster. (The details about the machine we run
the benchmark can be found on the benchmark page). After this happened
twice, we decided to postpone the
--fast test until next
Before going to the other benchmarks we know of, We would like to give some background to benchmarks:
It's very easy to write a test that shows ANY database to be best database in the world, by just restricting the test to something the database is very good at and not test anything that the database is not good at; If one after this publish the result with a single figure things is even easier.
This would be like we would measure the speed of MySQL compared to PostgreSQL by looking at the summary time of the MySQL benchmarks on our web page. Based on this MySQL would be more than 40 times faster than PostgreSQL, something that is of course not true. We could make things even worse by just taking the test where PostgreSQL performs worst and claim that MySQL is more than 2000 times faster than PostgreSQL.
The case is that MySQL does a lot of optimizations that PostgreSQL doesn't do and the other way around. An SQL optimizer is a very complex thing and a company could spend years on just making the optimizer faster and faster.
When looking at the benchmark results you should look for things that you do in your application and just use these results to decide which database would be best suited for your application. The benchmark results also shows things a particular database is not good at and should give you a notion about things to avoid and what you may have to do in other ways.
We know of two benchmark tests that claims that PostgreSQL performers better than MySQL. These both where multi-user tests, a test that we here at MySQL AB haven't had time to write and include in the benchmark suite, mainly because it's a big task to do this in a manner that is fair against all databases.
One is the benchmark paid for by Great Bridge.
This is the worst benchmark we have ever seen anyone ever conduct. This was not only tuned to only test what PostgreSQL is absolutely best at, it was also totally unfair against every other database involved in the test.
NOTE: We know that not even some of the main PostgreSQL developers did like the way Great Bridge conducted the benchmark, so we don't blame them for the way the benchmark was made.
This benchmark has been condemned in a lot of postings and newsgroups so we will here just shortly repeat some things that where wrong with it.
Tim Perdue, a long time PostgreSQL fan and a reluctant MySQL user published a comparison on phpbuider.
When we got aware of the comparison, we phoned Tim Perdue about this because there was a lot of strange things in his results. For example, he claimed that MySQL had a problem with five users in his tests, when we know that there are users with similar machines as his that are using MySQL with 2000 simultaneous connections doing 400 queries per second (In this case the limit was the web bandwidth, not the database).
It sounded like he was using a Linux kernel that either had some problems with many threads (Linux kernels before 2.4 had a problem with this but we have documented how to fix this and Tim should be aware of this problem). The other possible problem could have been an old glibc library and that Tim didn't use a MySQL binary from our site, which is linked with a corrected glibc library, but had compiled a version of his own with. In any of the above cases, the symptom would have been exactly what Tim had measured.
We asked Tim if we could get access to his data so that we could repeat the benchmark and if he could check the MySQL version on the machine to find out what was wrong and he promised to come back to us about this. He has not done that yet.
Because of this we can't put any trust in this benchmark either :(
The only benchmarks that exist today that anyone can download and run against MySQLand PostgreSQL is the MySQL benchmarks. We here at MySQL believe that open source databases should be tested with open source tools! This is the only way to ensure that no one does tests that nobody can reproduce and use this to claim that a database is better than another. Without knowing all the facts it's impossible to answer the claims of the tester.
The thing we find strange is that every test we have seen about PostgreSQL, that is impossible to reproduce, claims that PostgreSQL is better in most cases while our tests, which anyone can reproduce, clearly shows otherwise. With this we don't want to say that PostgreSQL isn't good at many things (It is!) We would just like to see a fair test where they are very good so that we could get some friendly competition going!
For more information about our benchmarks suite see See section 14 The MySQL Benchmark Suite.
We are working on an even better benchmark suite, including much better documentation of what the individual tests really do and how to add more tests to the suite.
Go to the first, previous, next, last section, table of contents.