Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: mysql - OT

RE: mysql - OT

From: Goulet, Dick <>
Date: Tue, 12 Apr 2005 09:12:39 -0400
Message-ID: <>


        Innodb is an add on to MySql. It is not handled natively. Also the lack of the other features requires one to then put that login in the application layer, which is all well & dandy, but how many applications acquire bolt-ons over the years that don't follow the same logic. Also how many times does one have to manually "correct" data that once again will not follow the application logic thereby breaking the application? I've amassed a ton of scripts to "fix" data mess-ups that there external connections create. Having it all in the database prevents 100% of that & consequently makes the application more bullet proof & easier to maintain. We've one application here that uses MySql incidentally without Innodb, and it has to be manually purged & reinitialized periodically for just that reason. Poor design on the application side, I don't think so, poor implementation on the database side you bet. As a plaything MySql is OK, but outside of that it has no place in a production environment, especially one that cannot tolerate occasional outages.

-----Original Message-----
From: Mark Leith []=20 Sent: Tuesday, April 12, 2005 6:01 AM
Subject: RE: mysql - OT

But MySQL supports Primary and Foreign Key constraints - the only thing it doesn't support are check constraints, and I believe those are on the "TODO" for 5.1, although you could probably mimic them with a BEFORE UPDATE trigger in 5.0..=20 confirms this..=20

Many of the things that people are mentioning here (Mladen mentioned a lack of row level locking privately as well) are all actually supported by MySQL, and have been for quite some time - all within the InnoDB storage engine - since about version 3.23, which has been out around 4 years..=20

Foreign keys:

Row Level Locking:

Full ACID Transactions:

It's true that there is no native support for partitioning fully within the database, like Oracle, but again you can also mimic this functionality as well using MERGE tables, with an added extra bit of application side programming to point queries at the right table (typically with a date part in the name, and partitioned by a date range - for things like logging tables:

Now, I've protected MySQL enough - Here is what *I* think MySQL's major "downfalls" (not including those things coming along in 5.x) are:

  1. Lack of sequences.
  2. Lack of function based indexes.
  3. No job scheduling system.
  4. Lack of a *detailed* performance "information schema" (MySQL has a bunch of "SHOW" commands for system stats etc., but nothing like v$ or pg_stat* type views, although the INFORMATION SCHEMA is also coming along within 5.x).
  5. No indepth set of analytical functions, i.e DENSE_RANK, PARTITION BY, CONNECT BY etc.
  6. No native support for partitioning.
  7. Nothing like Materialized Views.
  8. No support for DBLINK type of functionality (Although 5.x is gaining the FEDERATED storage engine - which will allow distributed transactions across servers etc.)=20
  9. Cluster's NDB storage engine doesn't support disk based storage.

So, it is still very true that MySQL is not going to be a really big competitor to Oracle, or DB2, or, possibly, even SQLServer in the short term - but it really *is* a big competitor to PostgreSQL. MySQL 5.0 will pretty much wipe out the rest of the "oh but PostgreSQL has <insert feature here>" kick backs, and if you're planning on using an Open Source database within your company, wouldn't you prefer to have a central accountable company provide support, training, consultancy etc. to you, rather than some "local professional services" company that comes along with the PostgreSQL camp, but is not "responsible" for it?=20

I hope this helps clear up a few misconceptions that a *lot* of people (not just on this list) have about MySQL..



Mark Leith=20
Cool-Tools UK Limited

-----Original Message-----
[] On Behalf Of Goulet, Dick Sent: 11 April 2005 20:16
To: Jared Still
Subject: RE: mysql - OT


    Thank you. I'll put the flame thrower away.   _____ =20

From: Jared Still []=20 Sent: Monday, April 11, 2005 1:07 PM
Cc: Goulet, Dick; Subject: Re: mysql - OT

On 4/11/05, Pakhutkin, Maxim (Max) <> wrote:=20

        transactions, PITR, etc). It's very nice to keep all your code in the =3D

        application layer and only worry about true RDBMS stuff in the database. =3D

        The recent push on the part of MySQL to add stored procedure, etc, is =3D=20

        them trying to appease the people that demand them because MySQL

        apparently is trying to compete against the big guys now. However, their =3D

You won't find too many here that sympathize with that point of view.

The idea that an RDBMS should simply be a container for data and nothing more is rather naive IMO.

Constraints? History has shown that these should be in the database. Applications cannot be entrusted to enforce constraints.

Applications come and go and get modified. The integrity of the data=20 should be enforced by the database. This is really part of the application, but not a part that is often touched.

Primary keys, check constraints and foreign keys all fall into this category.

Partitioning. How are you going to do that in an application? This requires some of the aforementioned features.

There are always pieces of an app that would be best run in the database. It is much faster than external access, and works regardless of which=20
application is accessing the data.

MySQL will get there I believe, but for now it cannot compete with PostgreSQL, Oracle and SQL Server for features that should be required for any serious=20

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.6 - Release Date: 11/04/2005

--=20 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.6 - Release Date: 11/04/2005
-- --
Received on Tue Apr 12 2005 - 09:16:49 CDT

Original text of this message