If you've ever created foreign keys on an InnoDB table, you'll see it automatically creates indexes, if none exists, on the referenced columns in the parent table, and also in the foreign key columns in the child table. This article explains why both are needed.

MySQL has a great feature called "Query Cache" which is quite helpful for MySQL Performance optimization tasks but there are number of things you need to know.

First let me clarify what MySQL Query Cache is - I've seen number of people being confused, thinking MySQL Query Cache is the same as Oracle Query Cache - meaning cache where execution plans are cached. MySQL Query Cache is not. It does not cache the plan but full result sets. This means it is much more efficient as query which required processing millions of rows now can be instantly summoned from query cache. It also means query has to be exactly the same and deterministic, so hit rate would generally be less. In any case it is completely different.

Query cache is great for certain applications, typically simple applications deployed on limited scale or applications dealing with small data sets. For example I'm using Query Cache on server which runs this …

Just a note to say that the first generally-available/production release of Connector/J 5.0 is now available for download.

This branch has had the synchronization fixed up quite a bit (which should help with those using multi-threaded pool managers like C3p0), adds support for Statement.cancel() and Statement.setQueryTimeout() and adds support for XA distributed transactions as well.

We‘ve also started publishing our builds to Maven‘s repository (we just put in the request for 5.0.3 to be uploaded, so it might take a couple of days). Let me know if there‘s more you‘d like to see around that.

These builds also contain the complete coverage report and unit test results (against MySQL-4.0, 4.1, 5.0 and 5.1 on Redhat EL4, Solaris, Mac OS/X, Ubuntu LTS and Windows), which is why the archives are a bit bigger than they used to be.

You can download your copy today from …

Just a note to say that the first generally-available/production release of Connector/J 5.0 is now available for download.

This branch has had the synchronization fixed up quite a bit (which should help with those using multi-threaded pool managers like C3p0), adds support for Statement.cancel() and Statement.setQueryTimeout() and adds support for XA distributed transactions as well.

We've also started publishing our builds to Maven's repository (we just put in the request for 5.0.3 to be uploaded, so it might take a couple of days). Let me know if there's more you'd like to see around that.

These builds also contain the complete coverage report and unit test results (against MySQL-4.0, 4.1, 5.0 and 5.1 on Redhat EL4, Solaris, Mac OS/X, Ubuntu LTS and Windows), which is why the archives are a bit bigger than they used to be.

You can download your copy today from …

Just a note to say that the first generally-available/production release of Connector/J 5.0 is now available for download.

This branch has had the synchronization fixed up quite a bit (which should help with those using multi-threaded pool managers like C3p0), adds support for Statement.cancel() and Statement.setQueryTimeout() and adds support for XA distributed transactions as well.

We‘ve also started publishing our builds to Maven‘s repository (we just put in the request for 5.0.3 to be uploaded, so it might take a couple of days). Let me know if there‘s more you‘d like to see around that.

These builds also contain the complete coverage report and unit test results (against MySQL-4.0, 4.1, 5.0 and 5.1 on Redhat EL4, Solaris, Mac OS/X, Ubuntu LTS and Windows), which is why the archives are a bit bigger than they used to be.

You can download your copy today from …

A number of people have been recently been inquiring about MySQL FEDERATED tables or are having requirements that seem ideally met using FEDERATED tables, so I thought I would perform some testing and better acquaint myself with the behavior of this storage engine. The first thing I noticed was the spot in the documentation that said:

The structure of this table must be exactly the same as that of the remote table, except that the ENGINE table option should be FEDERATED and the CONNECTION table option is a connection string that indicates to the FEDERATED engine how to connect to the remote server.

So I wrote a little PHP script that does a SHOW CREATE TABLE on the base table, alters the ENGINE clause, and adds the appropriate CONNECTION clause. After all if it has to be exactly the same, I may as well automate it and eliminate typos as a source of error.

Then I wrote another PHP script which …

The AlwaysOn Conference kicked off at Stanford today.  I got in after midnight from OSCON in Portland and only got about 5 hours sleep, so maybe it was just me, but the first couple of sessions started out a bit slow.  But by mid-morning things were kicking into high gear and the afternoon sessions were excellent.  The t-shirt to suit ratio is the completely inverse of OSCON, but the panels were still very good, and focused on the business issues around Web 2.0, software as a services, open source, intellectural property, venture capital trends and so on.  If you're looking for hot new startups in the valley, or lessons from the big guns, this is the place.

I particularly enjoyed a panel led by Kara Swisher of the Wall Street Journal on user-generated …

In case you missed the news, Greenplum and Sun have teamed up to deliver a monster of a business intelligence/data warehousing appliance. What's it called? Well, that's the downside: "The Data Warehouse Appliance." If you're still awake after reading the name, you'll still be blown away by the performance (and the price). Those, at least, are interesting: capable of scanning 1 terabyte of data in 60 seconds and can easily scale to hundreds of terabytes of usable database capacity (10-50X performance boost over the Terradata/etc. competition).

What will it run you? Well, you won't find it at CompUSA, but it's pretty cheap (relative to the competition) all the same. From the press release:

Initial configurations will deliver usable database capacities of 10, 40 and 100TB. Pricing for the 40TB and 100TB configurations …

This article explains how I replaced file-based methods to ensure only one running instance of a program with MySQL's GET_LOCK function. The result is mutual exclusivity that works in a distributed environment, and it's dead simple to implement.