From: Matthew French (mfrench42 at domain yahoo.co.uk)
Date: Sat 14 Sep 2002 - 13:17:43 IST
Kevin Lyda argued:
> how about a mix of straight joins and left joins on five or six tables -
> several with many million rows?
When I looked at MySQL, it did not support left joins (which I called outer
joins).
> dunno about outer joins, but it does support transactions with some
> table types. mysql supports several backend table types.
I would have a real issue with transactions that are "partial". The most
important point of a transaction is that if something in the transaction
fails, then the database goes back to the same state from which the
transaction started. If a query updates a non-transactional table half way
through, and then fails later because of a primary key violation, does the
original update get rolled back?
> > In my mind, I tend to think of MySQL as the open source alternative to
> > Access databases, and Postgres as the alternative to SQL Server. I am
>
> i think that's a poor comparison. to begin with access isn't really
> client/server oriented, while that's the only way to use mysql.
I make the comparison more from a "how I would use the database" than from
what they actually offer. Access is not transactional, and it has a very
simple file based storage system. Similar for MySQL.
But Access also has all that sickly GUI stickiness that one would expect
from Microsoft, which MySQL does not. MySQL works a lot better in a shared
environment than access. So obviously there are limits to my comparison.
> wander into your local newsagent. if there's an alphyra credit card
> terminal that sells o2/vodafone/etc top-up pins, you'll have a chance to
> use mysql.
Access can also be very good at handling millions of rows - although it
depends of what.
I would be impressed if that database is comfortably handling 30
transactions a second. Size of data is not the only factor, it is how well
the DBMS manages many people trying to do many different things with the
database. If this can be done quickly and reliably with large data sets,
then I would class MySQL as an enterprise database. I know I do not put
PostgreSQL into this category, although it is getting close.
There is also another important factor here: what do you want to do with the
database? If a database is only being used to store information in an
organised form, then I would use one product. If a database is required to
answer many different queries whilst updating and replicating commits to hot
standby, then I would look at an entirely different product.
Another area that is often overlooked is persistent memory: many
applications need to store many gigabytes, or even terabytes, of information
in memory. Or they may want to store data in memory, but have the ability to
save it so that when the application is closed, the data is not lost.
Often programmers will use a DBMS as a substitute for memory. In this case
it would usually be better to use an object database. But saying "OODBMS" to
a programmer usually gets the same knee-jerk reaction that one gets by
saying "Linux" to a CIO.
Kevin later mentioned:
> the main guts of the application contain no sql, just calls to the
> database module with the data required. at a later date if we change the
> underlying db (changing the table layout, the data types, a different db,
> or whatever) it will only require a change to a single module.
This is where I do agree - on programming style: I find I use four basic
types in any application: string, integer, float and date/time. I rarely
need anything else. I would also frown on the idea of using stored
procedures for the reasons you mention. Most queries I use are "SELECT x
FROM y WHERE z". It is most probably my J2EE leaning, but I would put most
business logic in the application, not the database.
Having said that, the extra functionality of a database like PostgreSQL can
come in useful for one area: reporting. It is often easier to generate
different reports by using the database to do most of the grunt work, and
the application is only responsible for displaying the information. This is
where the rich variety of functions of, say, Postgres, are a real boon. But
porting these reports is usually quite trivial, so long as the new database
has equivalent functions.
To get back to the original topic: I believe people often use Oracle where a
much simpler solution will do. Sort of like trying to swat a mosquito with a
tank. :)
I would also say that in the majority of cases, MySQL and PostgreSQL will do
just as well any other database.
- Matthew
__________________________________________________
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
This archive was generated by hypermail 2.1.6 : Thu 06 Feb 2003 - 13:18:52 GMT