[Dbix-class] any factual comparisons of Oracle, PostgreSQL, MySQL ?

[Darren Duncan]

Dami Laurent (PJ) wrote:
> I have an important database migration project, aiming at replacing an 
> old DBMS by something more up to date, for mission-critical 
> applications. We are still at a very early phase of the selection 
> process, but some of the requirements will be : high-availability , 
> support for multi-values, fulltext search, data domains, inheritance, 
> CHECK constraints and triggers. The short list of candidate DBMS  is 
> likely to be Oracle, PostgreSQL and MySQL.

I recommend Postgres as your default choice, as it should do everything you 
want, its good quality and it is free.  Use version 8.4.x as a minimum (it adds 
lots of useful window functions plus WITH clauses), but you should use 9.0.x 
plus if you can; new major versions come annually with 9.1 about to hit beta.

Oracle should do everything but costs a lot of money.

You should avoid MySQL like the plague unless you have a clear business case for 
its use, such as that the project is already using it and has conversion costs.

I know for a fact that MySQL does not support CHECK constraints; it will parse 
them but then will silently ignore them, a source of bugs.

I discovered this recently when doing a work project that uses MySQL; I knew 
MySQL had some deficiencies, but I discovered a half-dozen more just in the last 
few weeks, and that was one of them.  Lack of CHECK is probably one of the 
biggest problems.  This can't be worked around.

I was using MySQL 5.0.x but all of the issues I mention are documented and apply 
to the latest versions also.

I also discovered that MySQL can't handle multiple references to the same 
temporary table in the same statement or stored procedure (it dies at runtime if 
you try; see 
http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html ).  To work 
around this, you either create and drop real tables, which hits your disk, or 
you make a lot more temporary tables that could be copies, or other messes.

I discovered MySQL will treat all subqueries (such as in/not-in) as being 
correlating even if they have no free variables, so re-executing them for every 
loop of the main query, giving you extremely terrible kill-it-after-waiting 
performance.  This can be worked around by putting the subqueries in FROM 
instead, where they aren't reexecuted, but that's more verbose.

I also discovered that MySQL needs hand-holding most of the time to determine 
the keys of subqueries so that join performance is not O(N^2), and the only way 
to do this is by creating a temporary/other table to put the intermediary in 
that has declared with the keys, since you can't give hints directly on subqueries.

Literature about MySQL issues mentions lots of things, but I've read those and 
all of the above I still had to discover for myself when trying to use it.

-- Darren Duncan