I have tables that should be 1:1 relations but occasionally, they become mismatched, one table having more records than the other. I have been wondering which is the most optimal way to delete things like this.

I have a delete using a join (item_id is primary key):

mysql> delete from items_keys using items_keys left join items using (item_id) where items.item_id is NULL;

vs. using a subquery:

mysql> delete from items_keys where item_id not in (select item_id from items);

Sometimes, the 2nd seems faster, but using a join seems more orthodox to me. I'm curious what people think about this.

If I make these into select queries (instead of delete) and run explain, I get:

mysql> explain select count(*) from items_keys left join items using (item_id) where items.item_id is …

PostgreSQL is a sleeping giant that is waking up. And instead of wondering around sleepy, they seemed to be jumping forwards in what seems leaps to the other guys, but are just natural steps for them. Heh, I seem to be in a dramatic poetic mood today, but I just wanted to get across with how impressed I am with what is going on with the PostgreSQL community, ever since version 8.0. To me PostgreSQL 8.0 was so critical since with it one of the key obstacles to more wide spread adoption was removed: There was finally a native easy to install version of PostgreSQL for windows. Not that I know many people that deploy on windows, but I do know a ton that develop on windows (which included me back then).

Anyways, since then they have been adding more and more features at a pace that is mind boggling compared to what MySQL seems to be doing with much greater ressources. They are opening up as the same time too, which only seems to be accelerating …

I have been having a serious look at the event scheduler in MySQL 5.1 lately, and I thought I would share some of my initial impressions. There is no doubt that having an event scheduler is a great bonus for the database. It's usefulness in the enterprise may be limited. While we are preparing to move forward with it, it hasn't been without struggle. I will describe the pros and cons we've encountered so far.

I haven't really followed SourceLabs for a little while now, and yet, strangely enough, they've been doing new and interesting things even without me watching ;-). SourceLabs "Self-Support Suite for Linux and Open Source Java" caught my eye, so I thought I'd learn more about it. I watched a demo that began with the slogan: "We're IT people...we don't call support". Made me laugh out loud... The useful thing about the Self-Support Suite is that it adds diagnostics to your applications. When developers have a support issue, the diagnostics results are used to search for the similar problem *and* the... READ MORE

I just arrived in Boston. My login at the Diesel Cafe is still good. I forgot to bring my Charlie Card, so I ended up buying another one. Oh well, I'm sure I'll be back.

The other MySQL PS guy shows up this afternoon sometime. The gig starts tomorrow.

JetBlue is the win for seat pitch. Unlike AA or NWA, my knees were not jammed into the back of the seat in front of me. Unfortunately, I ended up in possibly the worse seat for a red-eye: middle seat in front of the exit row. Thus I couldn't lean against the bulkhead, and I couldn't recline the seat. My own fault too, TripIt had a link to a good site for seating advice, and I didn't check it. If I had been paying attention, I could have switched to a window seat 2/3s the way back, and slept much better.

There is some sort of big Sun/MySQL/OpenSource business cocktail thing here in …

I have used the ~/.ssh/authorized_keys file with OpenSSH for ages. However, I always treated it as just a dumb list of keys, where I would dump my public key whenever I needed access to a new account or host.

But today I learned from this blog entry that there is a bit more to it. Details are available from `man 8 sshd'.

Basically, each key can be prefixed by a list of options which restrict the kind of access granted to a connection attempt providing the given key.

Of particular interest is the command="/usr/local/bin/foobar" option. This makes sshd run the given command on connect, disabling the normal login shell or direct execution of commands supplied by the remote user. There are also a few further options for disabling port forwarding, …

MySQL 5.1 offers new information tables such as GLOBAL_STATUS. This can be used to report certain performance metrics, such as the number of queries processed per second:

SELECT MAX(                                         -- use MAX to force aggregation
           IF(variable_name='Questions'             -- no. of queries sent to server
           ,   CAST(variable_value AS unsigned)     -- make integer value
           ,   0                                    -- ignore if not 'Questions'
           )
       )
/                                                   -- divide by
       MAX(                                         -- use MAX to force aggregation
           IF(variable_name='Uptime'                -- no. of seconds the server is up
           ,   CAST(variable_value AS unsigned)     -- make integer value
           ,   0                                    -- ignore if not …

From time to time we get the question how to split a query into a several smaller queries and unifying the result-set before we send it back to the client.

As the client only expects to get one result-set, we have to merge the result-sets from the server into one, like this:

First we need a storage for the result-set we want to build:

res = { }

Each connection gets its own one. We declare it outside of the functions as we want to share it between the result-sets of the same connection.

As an example let me just duplicate a query and send it to the server twice:

function read_query(packet)
    if packet:byte() ~= proxy.COM_QUERY then return end

    local q = packet:sub(2)

    res = { }

    if q:sub(1, 6):upper() == "SELECT" then
            proxy.queries:append(1, packet)
            proxy.queries:append(2, packet)

            return proxy.PROXY_SEND_QUERY
    end
end …

There were some funny moments during the conference at Rome University.
Before Marten's arrival, we built an impressive heap of fluff dolphins on the desk.

For convenience, Marten used my laptop (an Apple MacBook) for his presentation, and he remarked about "open source enthusiasts who use closed source software". Mac OSX is, indeed, not open source, but it is the friendlier closed source operating system around. And since I am not a zealot, but I use what is best for me, I can cope withApple. My Mac has all the applications I am used to in Linux, with more pleasant graphics, media, and networking features. This is my personal opinion, and YMMV. Back to the topic.

After the presentation, there was a Q&A session. When Marten said he was ready to take questions, I addressed the audience in Italian, saying that we could …

Today I have uploaded Q4M (a Queue for MySQL) 0.6, which is basically a performance-improvement from previous releases. Instead of using pread's and a small user-level cache, Q4M (in default configuration) now uses mmap for reads with a reader/writer lock to improve concurrency.

I also noticed that it would be possible to consume a queued row in one SQL statement.

SELECT * FROM queue_table WHERE queue_wait('queue_table');

This statement actually does the same thing as,

if (SELECT queue_wait('queue_table') == 1) {
  SELECT * FROM queue_table;
}

But since the former style requires only one SQL statement (compared to two statements of the second one), it has much less overhead.

And combining these optimizations together, consumption speed of Q4M has nearly doubled from previous post (or trippled from 0.5.1) to over 57,000 rows per second. …