Re: Admin for E-MAIL users only
On Sun, Jul 07, 2002 at 06:44:06PM +1000, KevinL wrote:
> #include <twocents.h>
>
> The usual complaints regarding MySQL surface on large projects:
>
> 1) No transaction support (fixed with innodb, but up until recently a
> problem)
has the intellectual property dispute over that and the mysql trademark
been sorted out yet? last i heard, mysql and nusphere were still
fighting.
innodb means that mysql is more than just a fancy interface to the
filesystem. it's still got a very long way to go before it's a real
database server.
> 2) No sub-selects (which is a pain on a moderately complex system -
> makes for more awkward systems involving temporary table creation and
> joins)
that's about the only thing that can be worked around with client-side
programming.
> 3) No triggers/internal language ala pl/sql - again, this sucks for
> more complex systems, but is mostly work-round-able _iff_ you have
> full control over the code that talks to the database. The main body
> of code I work on, for instance, has a fairly complete OO layer
> overtop of the database code, so all interfacing with the database
> goes through a programmatic layer anyway, where trigger-like behaviour
> can be implemented.
no, you can't emulate triggers or built-in procedural language, just as
you can't emulate transaction support with client-side programming.
sometimes, e.g. for security reasons or to ensure integrity of related
tables, you MUST do things in the server, you can't rely on the client
to do the right thing.
> Having said that, it's faster than postgres in most situations, by all
> accounts (note: crass generalisation, I'm sure there's instances of the
> reverse too).
it's not even most situations. mysql is only faster than postgresql in
a very limited range of toy situations.
in the very simple case (i.e. READING a lot of data from one table by
one user, with little or no writing), mysql is faster. that's it. if
that's all you ever need to do, then mysql will be faster for you.
in real world situations, postgresql is MUCH faster because it works
smarter rather than working harder - e.g. you can do things in one SQL
query with postgres (or other real database) that requires multiple
queries with mysql. now each of those mysql queries might be slightly
faster than if postgres were doing them (and even that is debatable with
recent versions of postgresql) but the cumulative effect of having to do
two, three, four, or even six times as many queries really destroys
mysql's claim to greater speed.
for some interesting reading on postgresql's speed, try:
http://apachetoday.com/news_story.php3?ltsn=2000-08-14-008-01-PR-MR-SW
read the full article, it's worth it (but take it with a grain of salt
because it is basically a press release). here's a good teaser excerpt:
"On the ANSI SQL Standard Scalable And Portable (AS3AP) benchmark,
a rudimentary information retrieval test that measures raw speed
and scalability, Postgres performed an average of four to five
times faster than every other database tested, including two major
proprietary DBMS packages, the MySQL open source database, and
Interbase, a formerly proprietary product which was recently made
open source by Inprise/Borland."
a real article from someone who uses both postgres and mysql extensively
is:
http://www.phpbuilder.com/columns/tim20001112.php3
tim purdue (dba and programmer for sourceforge) explains how mysql isn't
coping with the sourceforge database, and what happened when he ported
it to postgresql:
"With that in mind, I decided to test out a full port of
SourceForge.net to Postgres. The site was written with a database
abstraction layer and it turned out to be a cinch to get it up and
running on Postgres, including a full import of all production data
from MySQL.
Not only did the site come up on the first attempt, but it ran
fine! In fact, our very first benchmarks showed Postgres running
6x faster than MySQL on a very database-intensive page (the "My
Personal Page" for logged-in users).
To make things interesting, we ran the exact same code base on
MySQL 3.23.26beta and Postgres 7.1 (CVS pre-beta version). The
SQL structures were identical, except that I had added Foreign
Key constraints to the database schema on Postgres (MySQL doesn't
really support foreign keys). This should have tilted the
performance in favor of MySQL, because MySQL doesn't do any data
validation on inserts/updates, but Postgres did in this test."
again, read the full article. there's a lot of useful info in it
whether you use mysql or postgres. it gets particularly interesting
where he talks about scalability - in short, mysql simply doesn't scale
well at all to multiple concurrent users.
btw, that's a followup to an earlier article by the same guy:
http://www.phpbuilder.com/columns/tim20000705.php3
in which he explains his reasons for choosing mysql over postgres a few
months earlier. also worth reading.
postgres is up to version 7.2 now, and is faster than it was back in
2000.
> Craig, I reckon calling MySQL a toy now innodb exists and is fairly
> stable is a bit harsh - given transactions, it's a capable database.
> It often still gets _used_ as a toy, but that's not it's fault.
it's not harsh at all - unless you count the truth as being harsh.
it still gets used as a toy because it is a toy.
> Triggers and pl/sql would be a nice add, but are not thoroughly
> necessary - and will, in fact, adversely affect your
> ability/likelyhood of writing a database-agnostic system (ie. you're
> more likely to lean on db-provided functionality, resulting in a
> system that's tied closely to one database instead of portable easily
> to MS/SQL or Oracle or whatever else).
the fact is that you're going to do that anyway. every db is different.
every db has a different variant of SQL...they might look superficially
similar but there are enough differences that you're going to have to
rewrite all but the simplest of queries in any migration.
as for the internal languages, at least postgresql's pl/sql clone is
similar (not identical) to oracle's...not that i can see many valid
reasons for migrating from postgres to oracle.
> (/me uses mysql and postgres, incidentally - more mysql than postgres,
> tho)
i've tried using mysql. i just can't see the point. it's a huge step
backwards with no benefit at all.
given that postgres exists, is MUCH faster in complex real-world usage
than mysql, i really don't understand why anyone uses mysql. why use a
toy when a real db is available that does everything that the toy does
and a whole lot more and has a far superier administration features?
i know that mysql gets all the press. but press coverage and popularity
aren't good reasons for picking a database, any more than they are good
reasons for picking an operating system.
craig
--
craig sanders <cas@taz.net.au>
Fabricati Diem, PVNC.
-- motto of the Ankh-Morpork City Watch
--
To UNSUBSCRIBE, email to debian-isp-request@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org
Reply to: