Re: MySql vs. Oracle

"Erik Pedersen" <erik_at_bid2day.no> wrote:
> Hi, I wonder is MySql controlled by transactions like Oracle or is that
 the
> reason that Oracle is much better on bigger systems.
>
> If thats not the reason can someone explain the differece for me?

At present, mySQL doesn't do transactions. It also doesn't support foreign keys, stored procedures, or sub-selects. These restrictions (their documentation suggests, and I have found it to be true) go a long way to keep you from doing wasteful things with your database.

As an example, I have a MS-Access database that I'm moving to mySQL. In Access, I had bunches of stored procedures that called each other... sums of unions of joins of other sums. I'd have chains of about 4 or 5 stored procedures. This caused a lot of computation when I wanted to query one of those procedures. When I decided to move the DB to mySQL, I had to figure out how to get what I needed without stored procedures. I was able to do it with just two intermediate tables that get updated much less often then they get queried. So, not only did I move to a much faster database, but the restrictions of mySQL forced me to change to a better-thought-out design of my DB.

So, I think the mySQL philosophy is "Yes, mySQL makes it harder to do some things.... but most of those things that become harder are stupid things that you shouldn't have done anyway.".

Now, that said, let me say that there's a tradeoff between reliability/robustness and speed in the DB world. One one end, you've got mySQL that requires about 5MB of space to install, doesn't require a whole lot of RAM, and runs really, really fast. Also, mySQL works on top of your existing filesystem (it doesn't require it's own partition with strange format), so moving the database to another machine is as easy as stopping the server, copying the files from the old machine to the new machine, and starting the daemon on the new machine.

On the other end you've got Oracle that is absolutely gargantuan. One of our faculty is always asking us to install some new Oracle app on his machine and the requirements border on the bizarre: 256MB of RAM, 10BG of hard disk space.... that kinda thing. However, Oracle probably has every topping you can get on a DB pizza.

In between those two, you have postgreSQL, Interbase, MS-SQL, DB2, etc...

Lately, PostgreSQL has seemed to compete very tightly with mySQL for the top spot as far as speed goes, and PostgreSQL offers transaction support and some other features that snobs consider essential parts of a "real" DBMS. In fact, I saw a recent benchmark that shows PostgreSQL outperforming mySQL when the concurrent connections gets past about 6 or 7. So, if mySQL ever doesn't cut the mustard for me, I'm going to PostgreSQL.

I wouldn't use Oracle if it were free. It's a hog.

• Joe