No subject

"For more complicated subqueries you can often create temporary tables to
hold the subquery. In some cases, however this option will not work. The
most frequently encountered of these cases arises with DELETE statements,
for which standard SQL does not support joins (except in sub-selects). For
this situation there are two options available until subqueries are
supported by MySQL Server."

InnoDB offers ACID transactions but error handling is not quite ANSI
standard.  Sometimes the whole transaction is rolled back, sometimes only
the offending statement. (see
http://www.mysql.org/documentation/mysql/bychapter/manual_Table_types.html#E
rror_handling for details) 

It would also be nice to have stored procedures/triggers.

Foreign keys would be good to. (available in InnoDB)

The other thing I'd like to see in MySQL is to be able to use proper
sequences instead of this AUTO_INCREMENT "feature".  It was a definite
surprise to have MySQL re-use id numbers after deleting rows from a table.
Once you know it happens, you can work around it, but it's still surprising
when it first happens.

One day I'll have another look at MySQL (when they fix the table locking
stuff perhaps), but in the meantime Postgres has far better features and has
always been "fast enough" for me.



-----Original Message-----
From: Matthew Hawkins [mailto:matthew at topic.com.au]
Sent: Friday, 5 April 2002 16:06
To: CLUG
Subject: Re: PostgreSQL vs MySQL?


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