Re: PostgreSQL or MySQL ?
- From: Bill Karwin <bill@xxxxxxxxxx>
- Date: 29 May 2007 22:48:22 -0700
On May 25, 10:38 am, Nis Jørgensen <n...@xxxxxxxxxxxxx> wrote:
http://dev.mysql.com/doc/refman/4.1/en/index.html
True. I am really wondering: Did 4.0 not have documentation? Why is that
documentation no longer available separately online?
Why don't you take a look at it? 3.23/4.0/4.1 are documented in one
lump -- even though only "4.1" appears in the URL above.
Regarding performance, on my current project we run a few hundred unit
tests against multiple database brands on a regular basis, including
MySQL and PostgreSQL. In my MySQL instance, InnoDB is the default
storage engine (the "slower" storage engine), and MySQL is still
noticably better on performance than PostgreSQL and any of the other
databases we support. Though I admit that being unit tests, with
dozens of setUp/tearDown operations, what I'm measuring is totally not
representative of typical RDBMS usage. So all I can conclude from
this is that MySQL is very quick when connecting and creating a half-
dozen tiny tables.
- Not being able to update multiple rows in one UPDATE statement, if the
intermediate state of the table violates a constraint. Example: UPDATE
mytable SET order = 2 - order WHERE order in (1,2);
Yes, that's a problem. Actually, I'm not certain they still suffer
from that in the most recent releases. Have to check to be sure. Ah
- they have UPDATE IGNORE now, which at least doesn't abort the UPDATE
operation, but it still doesn't apply the update as you would expect;
MySQL does not support deferred constraint checks.
- Not being able to use a subquery on the same table in an UPDATE
statement. Example: UPDATE mytable set order = 1+ (SELECT Max(order)
FROM mytable) where id = 123;
And more generally, reading and writing the same table in one
statement causes problems. The same applies to INSERT and DELETE.
- Not being able to rename a field with a FOREIGN KEY constraint on it
(no error message until trying to manipulate the data).
And related, any ALTER command incurs a complete table restructure.
- FOREIGN KEY constraints being silently ignored if the table is MyISAM.
This is documented that MyISAM doesn't enforce or even record such
constraints. But I agree -- if it doesn't work in that storage
engine, I wish it didn't silently accept the constraint declaration.
That's like false advertising.
Actually, I believe this feature (lack of Windows support) has been
crucial in achieveing the current level of quality of postgresql. I
strongly recommend it for any Open Source project aiming for excellence.
That's one thing that MySQL did do several years earlier than
PostgreSQL, and I believe it gave them "critical mass" with respect to
name recognition and market share. PostgreSQL probably will never
recover from that.
I agree that MySQL is behind PostgreSQL when it comes to technical
features. That's not a surprise to anyone who knows both products,
and I wasn't arguing anything different.
Still, MySQL supports *both* CREATE...IF NOT EXISTS and DROP...IF
EXISTS! Not a really sophisticated feature, but awfully handy. I
discovered PostgreSQL only has the latter, and only added it in 8.2.
Kind of doesn't make any sense to have one and not the other, does
it? ;-)
Regards,
Bill Karwin
.
- References:
- Re: PostgreSQL or MySQL ?
- From: Nis Jørgensen
- Re: PostgreSQL or MySQL ?
- From: Bill Karwin
- Re: PostgreSQL or MySQL ?
- From: Nis Jørgensen
- Re: PostgreSQL or MySQL ?
- Prev by Date: Re: Advanced SQL - Extracting values by months
- Next by Date: Help in understanding an alumni database.
- Previous by thread: Re: PostgreSQL or MySQL ?
- Next by thread: Re: some basic db questions
- Index(es):
Relevant Pages
|
|
