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 …

The OSCON talk & tutorial submission period ends in about a week on February 5. Please submit your proposals soon!

And woah - we've done a poor job at beating people over the head with the submission deadline! Usually we have many many times more submissions than slots, but as of right now it's quite the other way around (which probably is normal too, I don't remember the stats for when the submissions come in). In the categories I'm particularly interested in there are only a handful of submitted Perl talks and only one(!) MySQL talk submitted to the databases track. The Ruby submissions are pretty sparse too, as of now. We'd like some more. :-)

Many more talks are submitted to "Web applications, client- and server-side" already, but as far as I'm concerned it absolutely won't hurt to have more to pick from. ;-)

…

I find myself thinking about Federated and about how to improve it's connection pooling. Whatever is done should be made safe for transctions. Unfortunately, we cannot suspend and join XA transactions which is a pity as it would make it much easier.

Answering yesterday to some newsgroup question about MySQL I have answered about the problem of the full-text searches in natural language.

Create and populate of a simple table with a full-text index.

mysql> create table ft_test(
    -> id int not null auto_increment,
    -> string text,
    -> fulltext index(string),
    -> primary key(id));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into ft_test(string) values('forza inter'),('inter'),('viva inter'),('scudetto'),('champions league');
Query OK, 5 rows affected (0.10 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from ft_test;
+----+------------------+
| id | string           |
+----+------------------+
|  1 | forza inter      | 
|  2 | inter            | 
|  3 | viva inter       | 
|  4 | scudetto         | 
|  5 | champions league | 
+----+------------------+

Let's try first a search using the full-text index.

 …

Answering yesterday to some newsgroup question about MySQL I have answered about the problem of the full-text searches in natural language.

Create and populate of a simple table with a full-text index.

mysql> create table ft_test(
    -> id int not null auto_increment,
    -> string text,
    -> fulltext index(string),
    -> primary key(id));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into ft_test(string) values('forza inter'),('inter'),('viva inter'),('scudetto'),('champions league');
Query OK, 5 rows affected (0.10 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from ft_test;
+----+------------------+
| id | string           |
+----+------------------+
|  1 | forza inter      |
|  2 | inter            |
|  3 | viva inter       |
|  4 | scudetto         |
|  5 | champions league |
+----+------------------+

Let’s try first a search using the full-text index.

 …

The following is what i wrote on “43people.com” about the boss. I thought it was worth keeping in my own archives, since it’s actually a story about my life as it pertains to Mr. Bezos.

Back a few years ago, I was taking some classes down in Edmonds. The one I’m thinking of in particular was on the care and feeding of unix. We were using red hat linux 6.0 or some crufty version that wasn’t so crufty at the time.

Anyway, the prof didn’t require that we buy any books, but he made some suggestions. And he also suggested that we buy them on this new fangled “Internet” thing through a few of his friends down south in Seattle at this place called Amazon.com.

And thus was my introduction to O’Reilly and Associates. I soon thereafter bought a book called “ …