Полезная информация

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


25 How MySQL Compares to Other Databases

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 docs@mysql.com.

For a list of all supported limits, functions, and types, see the crash-me Web page at http://www.mysql.com/information/crash-me.php.

25.1 How MySQL Compares to mSQL

Performance
For a true comparison of speed, consult the growing MySQL benchmark suite. See section 13.7 Using Your Own Benchmarks. Because there is no thread creation overhead, a small parser, few features, and simple security, mSQL should be quicker at: Because these operations are so simple, it is hard to be better at them when you have a higher startup overhead. After the connection is established, MySQL should perform much better. On the other hand, MySQL is much faster than mSQL (and most other SQL implementations) on the following:
SQL Features
Disk Space Efficiency
That is, how small can you make your tables? MySQL has very precise types, so you 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 100,000,000 records, saving even one byte per record is very important. mSQL2 has a more limited set of column types, so it is more difficult to get small tables.
Stability
This is harder to judge objectively. For a discussion of MySQL stability, see section 1.7 How Stable Is MySQL?. We have no experience with mSQL stability, so we cannot say anything about that.
Price
Another important issue is the license. MySQL has a more flexible license than 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.)
Perl Interfaces
MySQL has basically the same interfaces to Perl as mSQL with some added features.
JDBC (Java)
MySQL currently has a lot of different JDBC drivers: The recommended driver is the mm driver. The Resin driver may also be good (at least the benchmarks looks good), but we haven't received that much information about this yet. We know that mSQL has a JDBC driver, 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. Because 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 of the MySQL Reference Manual (see section F MySQL change history). It should be pretty obvious which one has developed most rapidly.
Utility Programs
Both mSQL and MySQL have many interesting third-party tools. Because it is very easy to port upward (from mSQL to MySQL), almost all the interesting applications that are available for mSQL are also available for MySQL. MySQL comes with a simple msql2mysql program that fixes differences in spelling between mSQL and MySQL for the most-used C API functions. For example, it changes instances of msqlConnect() to mysql_connect(). Converting a client program from mSQL to MySQL usually takes a couple of minutes.

25.1.1 How to Convert mSQL Tools for MySQL

According to our experience, it would just take a few hours to convert tools such as msql-tcl and msqljava that use the mSQL C API so that they work with the MySQL C API.

The conversion procedure is:

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

Differences between the mSQL C API and the MySQL C API are:

25.1.2 How mSQL and 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 from the mSQL protocol are listed below:

25.1.3 How mSQL 2.0 SQL Syntax Differs from MySQL

Column types

MySQL
Has the following additional types (among others; see section 7.7 CREATE TABLE Syntax):
MySQL also supports the following additional type attributes:
mSQL2
mSQL column types correspond to the MySQL types shown below:
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-byte versions are available.
UINT INT UNSIGNED
DATE DATE. Uses ANSI SQL format rather than mSQL's own format.
TIME TIME
MONEY DECIMAL(12,2). A fixed-point value with two decimals.

Index Creation

MySQL
Indexes may be specified at table creation time with the CREATE TABLE statement.
mSQL
Indexes must be created after the table has been created, with separate CREATE INDEX statements.

To Insert a Unique Identifier into a Table

MySQL
Use AUTO_INCREMENT as a column type specifier. See section 24.1.3.126 mysql_insert_id().
mSQL
Create a SEQUENCE on a table and select the _seq column.

To Obtain a Unique Identifier for a Row

MySQL
Add a PRIMARY KEY or UNIQUE key to the table and use this. New in Version 3.23.11: If the PRIMARY or UNIQUE key consists of only one column and this is of type integer, one can also refer to it as _rowid.
mSQL
Use the _rowid column. Observe that _rowid may change over time depending on many factors.

To Get the Time a Column Was Last Modified

MySQL
Add a TIMESTAMP column to the table. This column is automatically set to the current date and time for INSERT or UPDATE statements if you don't give the column a value or if you give it a NULL value.
mSQL
Use the _timestamp column.

NULL Value Comparisons

MySQL
MySQL follows ANSI SQL, and a comparison with NULL is always NULL.
mSQL
In mSQL, NULL = NULL is TRUE. You must change =NULL to IS NULL and <>NULL to IS NOT NULL when porting old code from mSQL to MySQL.

String Comparisons

MySQL
Normally, string comparisons are performed in case-independent fashion with the sort order determined by the current character set (ISO-8859-1 Latin1 by default). If you don't like this, declare your columns with the BINARY attribute, which causes comparisons to be done according to the ASCII order used on the MySQL server host.
mSQL
All string comparisons are performed in case-sensitive fashion with sorting in ASCII order.

Case-insensitive Searching

MySQL
LIKE is a case-insensitive or case-sensitive operator, depending on the columns involved. If possible, MySQL uses indexes if the LIKE argument doesn't start with a wild-card character.
mSQL
Use CLIKE.

Handling of Trailing Spaces

MySQL
Strips all spaces at the end of CHAR and VARCHAR columns. Use a TEXT column if this behavior is not desired.
mSQL
Retains trailing space.

WHERE Clauses

MySQL
MySQL correctly prioritizes everything (AND is evaluated before OR). To get mSQL behavior in MySQL, use parentheses (as shown in an example below).
mSQL
Evaluates everything from left to right. This means that some logical calculations with more than three arguments cannot be expressed in any way. It also means you must change some queries when you upgrade to MySQL. You do this easily by adding parentheses. Suppose you have the following mSQL query:
mysql> SELECT * FROM table WHERE a=1 AND b=2 OR a=3 AND b=4;
To make MySQL evaluate this the way that mSQL would, you must add parentheses:
mysql> SELECT * FROM table WHERE (a=1 AND (b=2 OR (a=3 AND (b=4))));

Access Control

MySQL
Has tables to store grant (permission) options per user, host, and database. See section 6.9 How the Privilege System Works.
mSQL
Has a file `mSQL.acl' in which you can grant read/write privileges for users.

25.2 How MySQL Compares to PostgreSQL

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 PostgreSQL can offer, you should use PostgreSQL.

25.2.1 MySQL and PostgreSQL development strategies

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.

25.2.2 Featurevise Comparison of MySQL and PostgreSQL

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:

Drawbacks with MySQL compared to PostgreSQL:

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.

Feature MySQL version
Subselects 4.1
Foreign keys 4.0 and 4.1
Views. 4.2
Stored procedures in multiple languages 4.1
Extensible type system. Not planed
Unions 4.0.
Full join. 4.0 or 4.1.
Triggers. 4.1
Constrainst 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:

For a complete list of drawbacks, you should also examine the first table in this section.

25.2.3 Benchmarking MySQL and PostgreSQL

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 --fast 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 vacuum() 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 measured separately.

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 PostgreSQL release.

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 :(

Conclusion:

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.