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.

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. Why index the parent table? When a row is inserted or updated in the child table, the parent table must be searched for a row whose referenced values match the values in the foreign key columns.

So, my previous entry which contained a performance tip regarding the use of prepared statements got a few good comments, and prompted me to write a follow-up post summarizing the various comments and expanding a bit on the problems with using prepared statements.

What Are Prepared Statements, Anyway?

OK, so the concept of prepared statements have been around for quite some time. The basic advantages of prepared statements (from a conceptual point of view, not necessarily a realistic point of view for MySQL) are that SQL statement parameters — in other words, the WHERE, INSERT or UPDATE SET constants supplied to the SQL statement — can be represented by a token, such as "?", and at run-time, the tokens are replaced with actual parameters after being escaped. The escaping of the variables is important to mitigate the threat of certain security attacks, such as SQL injection. Additionally, in theory, the prepared …

Here are the slides from my OSCON 2006 talk about MySQL Cluster and how MySQL turned it from closed source into open source. Enjoy.

Lars Thalmann: Becoming Open Source

OSCON 2006 workshop

Building “Sane” Query Interfaces, by Mark Newsome e-mail is newsome@acm.org

Insane query interfaces have no help, no format, no range, and no help on how many results will be returned.

Better — pull-downs with a descriptive default like “select state”. Find min and max date from db. Gives a count for how many items in the pulldown menu. UI automatically reverts to use a popup button when pulldown gets too long.

“Refine” button can be pressed when the form is filled, and it will get a count, so you can “preview” your results. When the “refine” button is pressed, it refines EVERYTHING — ie, the counts for the range for the other items. Very smart! (see the song database demo below, when you enter in a lyrics search and click refine, it repopulates the pulldown menus with only the artists and titles that have the lyrics you requested). Also, if there’s …

There are 5 types of table partitions in mysql: range, list, hash, key and composite (or subpartitioning), i think the most commonly used type in the database world (at least in oracle) could be range partition, in this way a expression is evaluated and according to it different partitions are created with ranges of rows. The most common method to partition by range is using dates (by years,months,days) to group large amounts of rows from a table, mysql does not support partition by date type directly (oracle does), instead a function like year() or month() should be used to get an integer value.


Playing with this partition type, first i tried to create a table with a primary key on the id column and the partition column using insert_date, but MySQL generates an error if the column insert_date is not part of the primary key:

mysql> CREATE TABLE range_partition_tab (
 ->  id numeric …