I just noticed that the schedules for the Sessions and Tutorials of our upcoming MySQL Conference & Expo (which takes place on April 23rd-26th in Santa Clara, California) is now online. The program looks very promising and I am particularly excited that some of the sessions that I have helped to arrange made it into the final program:

• openBC / Xing: Growing Pains by Michael Otto and Erick Dennis from ePublica
• PrimeBase XT: Design and Implementation of a …

MySQL's FEDERATED storage engine is a fascinating example of the flexibility gained by abstracting the storage engine layer away from the rest of the server. FEDERATED tables allow you to access a table on another server as though it exists on the local server. However, the manual doesn't say much about how it really works. This article peeks under the hood of the FEDERATED storage engine.

I recently went to linux.conf.au in Sydney, and attended the MySQL Miniconf (mysql.conf.au). I thought I would recap on some highlights:

• 
  
• I leaned EXPLAIN EXTENDED [query] from Guy Harrison (can't believe i didn't know that one).
• I listened to Laura Thompson talk about the "update user set last_login = NOW()" problem that every social networking site seems to encounter. Her "proposed" solutions were roughly:
  
  
  • 
    
  • Use a different set of database systems to manage these "last logins" (often called "partition your application by function")
    
  • Lie, and give estimates ;-)
    
  
  
  

While I am a big fan of lying, I …

I (like several other of my co-workers) have over the past few days (since about Thursday/Friday IIRC) spent quite a few hours testing exam questions for the upcoming MySQL Cluster Certification exam.

Currently, I hold the record for the most correct answers (and number of questions that have been commented on). Yes, this is a direct challenge to other MySQLers to try and beat me.

For MySQL certifications, there is a giant pool of questions, a selection of which are selected for a particular candidate. So those brave few who go and test every single question spend a lot of time doing so to make sure the end exam is the best possible.

I’ve really grown to repsect the MySQL certs even more than I did before after being involved in creating one… we really try to make sure that to pass these you have to know and understand what you’re doing… no superficial stuff …

I recently spent a week in Nelson, New Zealand helping couchsurfing.com scale their database a little better. This was the first major LAMP project I've touched in a while, and it just happened to coincide with my one year anniversary working for MySQL.

When I arrived some things really scared me; They'd discovered that mysqldump would "lock" the entire database for the duration of it's execution, but if you dump the tables individually, they are only locked individually. Note: There is a reason mysql does this, it's called a consistent backup[1]!

I was in good company for the week though with Kasper & Joe. One of my best memories was the three of us pair programming through a shared screen (gnuscreen - we were multitasking). We had quite …

I'm not sure if I mentioned, but I'm speaking at PHP Quebec 2007!

I had two talks accepted, one on Performance optimisations in MySQL and the other on migration

The one one migrating data should be a lot of fun - it will be based on a talk I did at the MySQL miniconf this year in Sydney, but with a few more examples added.

The performance optimizations will have the "war story" touch, and be based on the performance scaling work I've been doing for couchsurfing.com as a volunteer.

I've created a patch for MySQL 5.0.33 to provide a function SHA2().Download it here: http://www.karwin.com/sha2.patch.gzIt really just calls out to the OpenSSL library for the digest functions. So you have to build MySQL from source with OpenSSL support enabled.You can use the function in SQL syntax like:SELECT SHA2('message', 256);The second argument is 224, 256, 384, or 512, depending on what

Working on subquery optimizations, got an idea how to speed up join execution a little. Read on.

The idea

Consider a query:

select * from t1, t2, t3 where t3.key=t1.col1 and t2.key=t1.col2

Suppose the join order is t1, t2, t3, i.e. the EXPLAIN is like

+-------+------+---------------+------+---------+--------------+-..
| table | type | possible_keys | key  | key_len | ref          |
+-------+------+---------------+------+---------+--------------+-..
| t1    | ALL  | NULL          | NULL | NULL    | NULL         |
| t2    | ref  | key           | key  | 5       | test.t1.col1 |
| t3    | ref  | key           | key  | 5       | test.t1.col2 |
+-------+------+---------------+------+---------+--------------+-..

The important property is that access to t3 is independent of access to t2. MySQL's nested loops join algorithm will run this as in this swimlane diagram:

Here we assume that

…