The Main Features of MySQL:
Internals and Portability
Written in C and C++.
Uses GNU Automake (1.4), Autoconf (Version 2.52 or newer), and Libtool for portability.
Works on many different platforms; APIs for C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl.
Fully multi-threaded using kernel threads, this means it can easily use multiple CPUs if available.
Very fast B-tree disk tables with index compression and thread-based memory allocation system.
Very fast joins using an optimised one-sweep multi-join, can mix tables from different databases in the same query.
In-memory hash tables which are used as temporary tables.
SQL functions are implemented through a highly optimised class library and should be as fast as possible!
Usually there isn't any memory allocation at all after query initialisation.
Security
All password traffic is encrypted connecting to a server.
A privilege and password system that is very flexible and allows host-based verification.
Scalability and Limits
Handles large databases. Maximum size for a table is 8TB (default 4GB).
Up to 32 indexes per table. Each index may consist of 1 to 16 columns or parts of columns.
The maximum index width is 500 bytes (this may be changed when compiling MySQL Server).
An index may use a prefix of a CHAR or VARCHAR field.
Connectivity
Clients may connect to the MySQL server using TCP/IP Sockets, Unix Sockets (Unix), or Named Pipes (NT).
All ODBC 2.5 functions and many others.
Localisation
All comparisons for normal string columns are case-insensitive.
The server can provide error messages to clients in many languages.
MySQL Server supports many different character sets that can be specified at compile and runtime.
Clients and Tools
Includes myisamchk, a very fast utility for table checking, optimisation, and repair.
All of the functionality of myisamchk is also available through the SQL interface as well.
All MySQL programs can be invoked with the --help or -? options to obtain online assistance.
Comparison of MySQL and PostgreSQL
MySQL Server offers the following advantages over PostgreSQL:
MySQL Server is generally much faster than PostgreSQL.
MySQL 4.0.1 also has a query cache that can boost up the query speed for mostly-read-only sites many times.
MySQL Server is used more in production environments than PostgreSQL.
MySQL has more APIs to other languages and is supported by more existing programs than PostgreSQL.
MySQL Server works on 24/7 heavy-duty systems. In most circumstances you never have to run any cleanups on MySQL Server.
PostgreSQL doesn't yet support 24/7 systems because you have to run VACUUM once in a while to reclaim space from UPDATE
and DELETE commands and to perform statistics analyses 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.
Please note: in PostgreSQL version 7.2, basic vacuuming no longer locks tables, thus allowing normal user access during the
vacuum. A new VACUUM FULL command does old-style vacuum by locking the table and shrinking the on-disk copy of the table.
MySQL Server supports more of the standard ODBC functions than PostgreSQL.
MERGE tables gives a unique way to instantly make a view over a set of identical tables and use these
as one. This is perfect for systems where you have log files that you order, for example, by month.
The option to compress read-only tables, but still have direct access to the rows in the table,
gives better performance by minimising disk reads. This is very useful when you are archiving things.
MySQL Server is coded from the start to be multi-threaded, while PostgreSQL uses processes.
Context switching and access to common storage areas is much faster between threads than between
separate processes. This gives MySQL Server a big speed advantage in multi-user applications and
also makes it easier for MySQL Server to take full advantage of symmetric multiprocessor (SMP) systems.
MySQL Server has a much more sophisticated privilege system than PostgreSQL. While PostgreSQL only supports
INSERT, SELECT, and UPDATE/DELETE grants per user on a database or a table, MySQL Server allows you to define
a full set of different privileges on the database, table, and column level.
MySQL Server also allows you to specify the privilege on host and user combinations.
MySQL Server supports a compressed client/server protocol which improves performance over slow links.
All MySQL table types (except InnoDB) are implemented as files (one table per file), which makes it really
easy to back up, move, delete, and even symlink databases and tables, even when the server is down.
Tools to repair and optimise MyISAM tables (the most common MySQL table type). A repair tool is only needed
when a physical corruption of a datafile happens, usually from a hardware failure. It allows a majority of
the data to be recovered.
Upgrading MySQL Server is painless.
You don't need to dump/restore your data, as you have to do with most PostgreSQL upgrades.
Drawbacks with MySQL Server compared to PostgreSQL:
The transaction support in MySQL Server is not yet as well tested as PostgreSQL's system.
Because MySQL Server uses threads, which are not yet flawless on many OSes, one must either
use binaries or carefully follow our instructions to get an optimal binary that works in all cases.
Updates that run over multiple tables are harder to do in MySQL Server. This will, however, be fixed
in MySQL Server 4.0.2 with multi-table UPDATE and in MySQL Server 4.1 with subselects.
In MySQL Server 4.0 one can use multi-table deletes to delete from many tables at the same time.
PostgreSQL currently offers the following advantages over MySQL Server:
Features MySQL version
-----------------------------------------------------------------------------------------------------
Subselects 4.1
Foreign keys 4.1 (3.23 with InnoDB)
Views 5.0
Stored procedures 5.0
Triggers 5.0
Unions 4.0
Full join 4.1
Constraints 4.1 or 5.0
Cursors 4.1 or 5.0
R-trees 4.1 (for MyISAM tables)
Inherited tables Not planned
Extensible type system Not planned
Mail comments to:css-dsg@fnal.gov
Last Modified December 19, 2002