Changes in release 3.23.x (Stable)

The 3.23 release has several major features that are not present in
previous versions. We have added three new table types:

MyISAM
A new ISAM library which is tuned for SQL and supports large files.
BerkeleyDB or BDB
Uses the Berkeley DB library from Sleepycat Software to implement
transaction-safe tables.
Innodb
A transaction-safe table handler that supports row level locking, and
many Oracle-like features.

The following will not yet work in MySQL: 

SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2 where table1.id=table2.id);

However, in many cases you can rewrite the query without a 
sub-select: 

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL

Note that foreign keys in SQL are not used to join tables, but are
used mostly for checking referential integrity (foreign key 
constraints). If you want to get results from multiple tables from a 
SELECT statement, you do this by joining tables: 

SELECT * from table1,table2 where table1.id = table2.id;

See section 7.20 JOIN Syntax. See section 9.3.6 Using Foreign Keys. 

The FOREIGN KEY syntax in MySQL exists only for compatibility with 
other SQL vendors' CREATE TABLE commands; it doesn't do anything. The 
FOREIGN KEY syntax without ON DELETE ... is mostly used for 
documentation purposes. Some ODBC applications may use this to 
produce automatic WHERE clauses, but this is usually easy to 
override. FOREIGN KEY is sometimes used as a constraint check, but 
this check is unnecessary in practice if rows are inserted into the 
tables in the right order. MySQL only supports these clauses because 
some applications require them to exist (regardless of whether or not 
they work). 

There are so many problems with foreign key constraints that we don't 
know where to start: 

Foreign key constraints make life very complicated, because the 
foreign key definitions must be stored in a database and implementing 
them would destroy the whole ``nice approach'' of using files that 
can be moved, copied, and removed. 

The speed impact is terrible for INSERT and UPDATE statements, and in 
this case almost all FOREIGN KEY constraint checks are useless 
because you usually insert records in the right tables in the right 
order, anyway. 

There is also a need to hold locks on many more tables when updating 
one table, because the side effects can cascade through the entire 
database. It's MUCH faster to delete records from one table first and 
subsequently delete them from the other tables. 

You can no longer restore a table by doing a full delete from the 
table and then restoring all records (from a new source or from a 
backup). 

If you use foreign key constraints you can't dump and restore tables 
unless you do so in a very specific order. 

It's very easy to do ``allowed'' circular definitions that make the 
tables impossible to re-create each table with a single create 
statement, even if the definition works and is usable. 

It's very easy to overlook FOREIGN KEY ... ON DELETE rules when one 
codes an application. It's not unusual that one loses a lot of 
important information just because a wrong or misused ON DELETE rule. 

The only nice aspect of FOREIGN KEY is that it gives ODBC and some 
other client programs the ability to see how a table is connected and 
to use this to show connection diagrams and to help in building 
applicatons. 

Just ignore what they say about foreign keys, they don't know what
they're talking about.  I thought it was some sort of warped April
Fool's joke or a vandal's hack on their website when I first read it.

As far as answering the actual question raised by Anthony, though -
InnoDB looks like it could be a very good back end.  My guess is that
it will require some seasoning in the field before it lives up to its
potential or can really be considered reliable.  This isn't a knock on
Innobase, it's just reality - RDBMS engines are hard to get right and
concurrency problems are subtle and frequently only crop up
occasionally and are very difficult to reproduce.

I've not looked at their website in a couple of months, but in some
regards its still incomplete.  There's an 8KB limit on either column
or row length, for instance (sound familiar, ye olde Postgres
veterans?).  They plan to work their way past various restrictions but
expect it to take some time.

The InnoDB back end is an Oracle-style storage manager, with row-level
locking, following an overwriting paradigm with rollbacks etc done
from redo logs.

One of the reasons I think it might be good is that originally it had no
connection with MySQL.  In fact, I think they intended to write their
own full-blown RDBMS at one point.  Whatever the specifics, it's been
glued to the MySQL front end.  The folks doing InnoDB obviously have a
somewhat more reasoned and mature notion of what an RDBMS should be
than Monty and the rest of the MySQL crowd.
They firmly believe in the importance of ACIDity, for instance, and
probably laugh as heartily as we do at some of the stupid things the
MySQL folk
have said in the past.

There's still the little problem of the weakness of the SQL dialect
supported by MySQL, as others have pointed out.

Folks who get bad numbers with PG frequently are using default configuration values,  in particular the default value of 64 blocks of shared buffer cache.  Also PG 7.1 gives us about a 10x improvement in raw insertion speed, due to the addition of REDO logging, so it's not clear that InnoBase is actually faster than PG 7.1 from his example.

Monty's published benchmarks were achieved by configuring MySQL in order to improve performance, i.e. not in its "out of the box" configuration.  His PG numbers were run using default values.  That was one of the things that caused me to think of him as being somewhat sleazy - particularly when he defended the practice.

The speed of the MySQL interpreter is partly due to the fact that it does very little optimization.  Simple queries have always been fast in MySQL, but its performance on queries with complex joins on several  tables has always been poor.  Monty's benchmarks have always emphasized simple queries.

PG has a storage manager abstraction, but it's at a lower level than the abstraction in MySQL.  The tuple storage format's pretty much hard-wired, so it wouldn't really be possible to drop the InnoBase backend into PG at this level.

On the other hand the parser and semantic analyzer present another layer one could plug into.  It's not as cleanly defined and frankly the PG parser's a royal kludge and an ugly mess anyway.

Of course, before doing so one would needs to ask whether or not there's actually any thing inherently wrong with the PG backend in the first place.  I don't think there is, myself.

The numbers mean nothing - I was just posting Tuuri's post to develop some of Don's statements about InnoDB and refuting some others.  (Namely that the InnoDB folks are closer to the MySQL folks than Don thought.)

Funny thing about performance claims - the rule of thumb I've developed is that groups won't change tools until there's a clear 1.7 to 2 orders-of-magnitude improvement.  Anything less is just too much bother for porting.  MySQL's harping is fairly irrelevant b/c of that (even ignoring the fact that it's a kid DB).

I actually wasn't trying to start a flame war at all. I was relating
what I had read in the mysql mailing list regarding Innodb and wanted
a true, unbiased answer. I have found that most commercial software
companies expand the truth as do fledgling open source companies such
as GreatBridge and MySql.

Pointing me to a URL over a year old does not really answer the
question of whether the new database format is any good or not. For
the application that I am working on which is mostly reporting, mysql
is actually ok (execept for adding columns which can take a while).
Multi-version concurrency control, for my app really has no value. The
company I am doing this for has a site license for MS SQL Server, but
I would rather avoid it if possible.

Microsoft research as a book on their website (their monopolistic
profits actually bought some research for once).
http://research.microsoft.com/pubs/ccontrol/

And oh, I was thinking of putting openacs on the same machine...

I've always wondered whether there was some fundamental stupidity lurking behind the scenes within the MySQL camp that makes them unable to understand ACID issues...

everybody here knows the community's attitude about mysql. so their developers lie and they don't know what they're talking about. maybe they finally got a clue. i checked and that's not quite a crime. in the grand scheme of things, they have neither soiled our good names
nor insulted our families (and beleive me, i have a very lenient scale for determining whether someone's done such a thing.)

there are probably a lot of people coming to the site with very sincere hopes of using it to build sites. they already know apache, mysql and perl and would like to use those skills. but they get here and they find out they need to learn AOLserver, PG and Tcl. Immediately they're probably intimidated by the necessity of learning three new tools. And they're probably more intimidated by seeing these fanatics crap all over one of their favorite tools, MySQL.

I love seeing Apache made available for these people as well as our intent to support other RDBMS's, which we are doing and will be absolutely great. If MySQL ever gets it's act together I think we would jump at the chance of folding it in.

so why do we keep looking like assholes whenever someone asks whether mysql is finally good enough?

talli

For those looking at InnoDB, you might find the interview with Heikki Tuuri, its implementor, at dbdebunk.com (excellent site with frequent contributions by C.J. Date) very interesting: http://www.dbdebunk.com/innodb1.htm