Some things MySQL is missing (or aren't implemented well):
sub selects
constraints (incl. foreign key)
triggers
stored procedures
transactions*
row level locking!
* The newer versions of MySQL do have transaction but are only available w/
a certain table type and are newly implemented (so may be buggy).
What MySQL has going for it is uberfast selects, and if your inserts are
very limited then those are fast as well. The lack of row level locking is a
MAJOR issue if you plan to have lots of inserts/updates as currently it has
to lock the entire table (NuSphere is helping out w/ this so it should be
available in version 4 sometime soon). MySQL treats records at the atomic
level, if you delete a record and you want cascading deletes through tables
that have tha as a foreign key it must be done programatically, there are no
relations just records.
Postgre is an ACID compliant database, and with its compliance and features
come addition overhead for almost every action you can think of. A
persistant connection improves speed enourmously for Postgres in many cases
(not much at all for MySQL) as do the use of stored procedures (by reducing
connection overhead and in the second it also improves execution time
internally). For anything but the bulletin board style website I preffer
Postgres (or Oracle if it's available) due mainly to foreign key constraints
and stored procedures: The amount of coding time saved letting the database
do what it should do and not having to code it for each application can be
enormous. This is the main reason why Postgre is slower, b/c it must check
for foreign key validity, other constraints, triggers, etc. If you code your
SQL well though and use stored procedures liberally, the performance cost
can be minimized and be well worth it.
As a note what specifically you are trying to do, if your database structure
looks to be rather simple then MySQL may be more to speed. It's rather
simple which can mean quick implementation if you don't have too many table
relations to worry about. And it's _very_ hard to get a dbms with such fast
retrieval.
-----Original Message-----
From: Toby Tremayne [mailto:[EMAIL PROTECTED]]
Sent: May 23, 2001 00:52
To: CF-Linux
Subject: little guy
This may seem like a naive question, but I'm a little lost.
I've been scanning the archives and benchmarks and all kinds of things for
DAYS now, trying to work out whether to go with postGres or mySQL as the
database backend for my own personal website, which is a writers site. The
site is growing well, but it's not database intensive, it doesn't require
millions of inserts or 100000 record selects every five seconds. Users
submit text data which gets inserted, make minor updates, and then search
and list using select queries.
Everything I'm seeing in all the speed comparisons etc etc seems to be
focused toward people looking for huge, mission critical speed reliant down
to the millisecond databases. I simply have a couple of functional
quandaries - myaql doesn't implement foreign key constraints well, but in
the benchmarks postgresql seems slower - what I want to know is for the kind
of functionality I'm going to be using, is the "speed" difference between
mysql and postgresql even going to be noticably at that level? Or is it
something that should factor heavily in my decision?
Toby Tremayne
Code Poet and Zen Master of the Heavy Sleep
Show Ads Interactive
359 Plummer St
Port Melbourne
VIC 3207
P +61 3 9245 1247
F +61 3 9646 9814
ICQ UIN 13107913
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-linux%40houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_linux or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
