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 a …

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

…

Sweden to set up embassy in Second Life
I reckon that's very cool. I'd already heard about some people making their living "in there".

MySQL AB today reported another milestone in its 11-year history with record results for the year ended December 31, 2006. The privately-held company continued to deliver rapid revenue growth -- fuelled by the delivery of significant product, community and partner initiatives throughout the year. The recent launch of the new MySQL Enterprise subscription offering is especially drawing high praise among MySQL's customers for its innovative monitoring tools and low total cost of ownership (TCO).

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, MySQL’s only developer article about it is vague and unrealistic, and there are more questions than answers on the net about the engine’s features and behavior.

While speed was not the first goal of the new 5.1 Archive format (aka
version 3 of the engine), whenever space on disk is reduced the
overall performance goes up assuming that the new format doesn't
require chewing up the processor too much.

The schema was the following:
CREATE TABLE accesslog (
id int(10) unsigned NOT NULL ,
method varchar(255) default NULL,
bytes_sent varchar(255) default NULL,
protocol varchar(255) default NULL,
uri varchar(255) default NULL,
args varchar(255) default NULL,
hostname varchar(255) default NULL,
client varchar(255) default NULL,
Referer varchar(255) default NULL,
slash_user varchar(255) default NULL,
e2_node varchar(255) default NULL,
time datetime NOT NULL default '0000-00-00 00:00:00'
);

The schema type was selected because its the type of Schema in 5.0
that …