MySQL Moves to Prime Time

MagLev writes "MySQL, especially version 5.0, is popping up on the radar screens of database gurus who built their reputations and book sales using other SQL databases. Ken North, who did those ODBC performance benchmarks for Oracle, Sybase, and DB2, wrote a recent article about MySQL 5.0. The article profiles mission critical database software and discusses how well MySQL 5.0 fits the profile. It gives good marks to MySQL, except for Java and XML integration."

Comments

New features

[Spy der Mann]

(for the lazy)

        * capacity for very large databases
        * stored procedures
        * triggers
        * named-updateable views
        * server-side cursors
        * type enhancements
        * standards-compliant metadata (INFORMATION_SCHEMA)
        * XA-style distributed transactions
        * hot backups.

Re:New features

[Michalson]

Or more specifically ISO SQL-92, or any other SQL standard. Everyone else seems to be smart enough to be able to implement a well documented industry wide standard as their base. MySQL didn't even start supporting UNION until version 4.

Re:MySQL != SQL

[matt4077]

If MySQL supported only a subset of the SQL Standard (big IF since it does have stuff like nested queries, transactions, triggers etc now), thats the opposite of lock-in. Obviously, there is no harm in using only a subset of SQL and then moving to a different RDBMS. It's exactly the Oracles and Microsofts with their embrace & extend policy that makes it difficult to switch: Oracle has _every_ (well, most - not the excellent fulltext search) feature of MySQL, so there is no problem to switch.

Re:MySQL != SQL

[Sheetrock]

Bringing advanced SQL queries into MySQL and moving advanced (My)SQL queries out of MySQL.

In both cases, you want to look before you leap. Do some trials to see how long porting will take before giving a time estimate, test the new system thoroughly (although that's recommended practice for switching RDBs anyway).

That's not to say MySQL is the only platform where you risk lock-in. Database triggers can be hooked to implementation-specific things, for example. Unfortunately as with programming there are trade-offs to be made between optimization and portability and if you're pushing lots of tuples you opt for the former.

Re:I can think of a pretty big plus in the column.

[ProfaneBaby]

Postgres is Free, MySQL is tied to a silly dual license (viral GPL and commercial), neither of which is as Free as the 3-clause BSD.

Re:I can think of a pretty big plus in the column.

[Anonymous Coward]

If you're just downloading and using the software, BSD and GPL are *identical* (because you can ignore them both). Talk about how un-relational MySQL is, or how it gets in the way of a DBMS' fundamental purpose (data integrity) with it's bizarre misfeatures, but don't spread FUD about the GPL. 'kay?

Re:innodb and fulltext?

[jaiyen]

Not yet apparently, according to http://www.innodb.com/todo.php, but at least it looks like progress is being made.

In progress: Add FULLTEXT indexes on InnoDB tables. A sponsor for this project has been found, and a developer has been hired. Appears probably in 2006.

Re:Gosh

[consumer]

Fails to live up to ACID? MySQL has had ACID transactions for years now. If you didn't know this, you have no place commenting on MySQL at all. It has the same sort of MVCC transaction and locking support that PostgreSQL does, and has since version 3.23.

Re:Gosh

[kpharmer]

> There is a decent sized market out there where organizations don't need a
> complicated schema or fancy features.

then they might want to check out sqllite: a simple and completely free database *without* bizarre integrity problems.

> They just want to do your average query on a fairly large db, but do it fast,
> hella fast. They'd rather put MySQL on a fast proprietory filesystem. Stripe
> and load balance off some fast storage arrays. And just blast away.

if they're blasting away with mysql, then they aren't doing much with the data:
  - no parallel query capability
  - no memory tuning
  - no partitioning
  - no optimizer sophistication

In short, unless you've got extremely simple queries looking up small sets of rows - mysql is slow as a pig, and can't compete with the commercial products. Again, if you *know* what you're doing.

And assuming that you're interested in data integrity and are using the innodb database, then postgresql is just as fast. Possibly *much* faster if you're writing moderately complex queries with 5 or more tables.

The idea that mysql is fast is a myth that came from php kids playing with a database for the first time. Once you actually compare the products available today mysql has nothing going for it - except quite a lot of inexperienced fans. Which, I have to admit, is probably worth quite a bit.

not Postgres

[cpeterso]

Actually, "Postgres" is was a precursor to "PostgreSQL". The database started as a university research project called Ingress. A follow-on version was called Postgres (i.e., Post-Ingress). SQL support was added later; thus PostgreSQL (Postgres + SQL).

Re:Liked it, but don't use it anymore

[Just Some Guy]

SQLite has no advertising clause. postgresql does.

I call your bluff. Here's the entire, unedited PostgreSQL license (source their website):

PostgreSQL Database Management System (formerly known as Postgres, then as Postgres95)

Portions Copyright (c) 1996-2005, The PostgreSQL Global Development Group

Portions Copyright (c) 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

Where's this advertising clause you speak of? Or did you hear "BSD license" and drag out a decade-old complaint that's long since been addressed? That's as bad as people complaining that MySQL doesn't support transactions, except that's true under certain circumstances whereas your criticism is completely unfounded.