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
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
