PostgreSQL vs MySQL?

[Matthew Hawkins]

On Fri, 05 Apr 2002, Damien Elmes wrote:
> We also see MySQL fall over sometimes for no apparent reason. With its lack of
> real ACID support, I wouldn't want to use it in a place where the data was
> actually important.

I've never had MySQL fall over for no reason, though Postgres does it
repeatedly.  Also, using InnoDB tables available for the past 12 months
(iirc) in MySQL you get certified ACID compliancy.

Also, Postgres appears to have, in my experience and in the experience
of many other people (check that website posted recently with the
Postgres vs MySQL article), problems with corrupting data quite
frequently, forcing rebuilds.  My suspicion is that it has something to
do with the way postgres uses temporary tables and in some circumstances
will just blat out the original DB with the entries in the temporary
table.  It's all documented in the postgres doco, just funnily enough
never marked up in big blinking lights "WILL CAUSE MASSIVE DATA CORRUPTION".

Funny how postgres folks seem to be fond of bashing MySQL for not having
transaction support (even though it does now) and claiming theoretical
corruptions that simply don't happen in practice, while not dealing with
the planks in their own eyes.  Two words - Atomic operations.  It's all
documented in the MySQL doco.  Yes, you can't guarantee consistency over
a bunch of operations simply because each one is atomic.  That's why
MySQL has table locks - again, in the doco.  You get the same outcome as
you get from a transaction, just without all the massive overhead.

They're currently working on finer-grained locking (since obviously
table-level can hit performance) and implementing sub-selects.  Read the
mysql doco for a few methods to work around not having sub-selects
already.  They say:

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

can be rewritten as:

SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;

and:

SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2 where table1.id=table2.id);

can be rewritten as:

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL;

I'm not claiming its possible to rewrite every situation, but it
certainly seems valid to me to avoid using subselects wherever possible.
No-one likes rewriting sloppy code because it means admitting they wrote
sloppy code to begin with.  Get over it.

-- 
Matt