it's not that simple [LWN.net]

it's not that simple

Posted Apr 4, 2008 13:38 UTC (Fri) by i3839 (subscriber, #31386)
In reply to: it's not that simple by drag
Parent article: Sun Microsystems' Next Linux Move (Seeking Alpha)

All I'll say is that for teaching SQL to students, MySQL is rather unfortunate in its limited
support for standard SQL features. That it requires workarounds to let it accept otherwise
fine SQL code only make it more annoying. That MySQL isn't alwasy smart enough to optimise
more complex queries and thus forces you to write ugly queries instead, isn't very nice
either. Let's say that from the people who have experience with MySQL and other databases I
also don't hear great things about it either, to put it mildly, so it's not only my own
unfortunate experience. But I'm sure the newest version is better.

(Log in to post comments)

it's not that simple

Posted Apr 7, 2008 13:06 UTC (Mon) by liljencrantz (subscriber, #28458) [Link]

All databases have their warts. Postgres arguably has fewer warts than MySQL, but it's not
perfect either. For one thing, table creation and simple insert statements are often orders of
magnitude slower than in MySQL, in my experience. This is using the latest Postgres version in
Ubuntu dapper. Also, having to vacuum the tables to reclaim storage is a bit silly.

it's not that simple

Posted Apr 7, 2008 15:00 UTC (Mon) by nix (subscriber, #2304) [Link]

PostgreSQL table creation is a bit slower because it's transactioned :))) 
personally I'm willing to accept a bit of sloth to gain the benefits of 
being able to roll back on screwups. With transactioning, you can easily 
have any random thing create tables and vape them automatically on 
ROLLBACK: something which should have been the case in all relational DBs 
from the very start, I think.

Vacuuming has been automatic for about a year now.

it's not that simple

Posted Apr 7, 2008 17:39 UTC (Mon) by liljencrantz (subscriber, #28458) [Link]

I don't know about table creation, but MySQL inserts a lot faster even when using transactions
and InnoDB, at least in my experience. And vacuuming is still silly, even if it's done
automatically. Last time I checked, it has a noticable performance impact on any queries
running while vacuuming, meaning Postgres is not very good at handling continously high loads.

I like Postgres just fine, don't mean to slam it at all, every database product has some
warts. MySQL probably has bigger warts, but both are in my experience pretty nice systems to
deal with, unlike say... Oracle.

it's not that simple

Posted Apr 7, 2008 18:16 UTC (Mon) by nix (subscriber, #2304) [Link]

Yeah. Actually as far as I can tell you have two choices if you want 
transaction consistency: use multiversion concurrency control, or use 
locks. Locks have a *much* worse performance hit in the concurrent case, 
and annoy developers more, but a lower hit in the single-threaded case. 
It's a toss-up which to use :)

(Oracle moved to MVCC a while back, too, while keeping around a huge pile 
of grot to try to mollify people who were used to locking everything and 
messing around in horrible ways with 'rollback segments' and that crud.)