Please note that I am in no way affiliated with MySQL (AB). I am just a user of their products. Everything you read here, especially my explanations and conclusions are to the best of my knowledge, but there is a chance that they are awfully wrong!

I recently had to investigate the cause for a very long running delete operation in one of our applications. Apparently the time was spent in a single MySQL statement. While looking into the matter I exchanged numerous mails with the MySQL customer support, until I finally came up with a (much) improved version of the query. Along the way I learned some valuable information about MySQL's (or better InnoDB's) transaction levels.

Problem

The general problem looks quite simple:

• Three tables, let's just call them A (500.000 rows), B (4.000 rows) and C (180.000 rows)

• B contains detail information for …

If you haven't picked up a copy of Pro MySQL, Barnes and Noble has a great deal on a selection of books called "Web Coding Tools." Between now and August 9th you can get 40% off these books.

That makes Pro MySQL $29.99 ($26.99 for members), not a bad price. You can order online if a physical B & N isn't an option. If there's a store nearby and you can get a photo of the special display these books are in there may be an iPod nano in it for you.

I took a trip …

I was wracking my brain for a bit, and could not figure out how to do a partial outer join where I was limiting rows from one table. So, I went to post to the MySQL user list — in writing my post, I found the answer. This often happens. It’s not that I don’t have questions to ask the MySQL user list (or forums), it’s just that most of the time, while I’m writing up the question, I find my answer.

First, I simplified my question. Many people do this, but they leave out important information. The trick is to come up with a simplification that you test to make sure it breaks in the same way the more complex, “real” query breaks.

The problem is basically this:
I want an outer join of a static table with a data table, such that I get a report of how many rows in the data table match the static table — including “0″ if there are none, but for a subset of the data in the data table. Consider a …

I've been fortunate to be able to help Tim put together the Open Source Executive Briefing for the O'Reilly Open Source Convention this year. Fortunate because my views of open source tend not to stray far from "What will make my company more money today?" Tim, of course, tends to think in terms of years and decades, not days and weeks. So working with him has helped me see a bit farther out into the darkness to see where open source is going.

The picture is very, very bright.

However, most people will continue plodding along in their "open source is about commodification" mode, missing out on the bigger picture(s), unless they attend. There are very few events that I think can fundamentally change the way you look at, invest in, and monetize open source technology. This …

Are you a good PHP developer searching for a cool place to work?

OmniTI (where I work) employs several industry leaders, including Theo Schlossnagle, George Schlossnagle, Laura Thomson, and Wez Furlong. We do lots of interesting, challenging work for some of the largest and most recognizable names on the Internet. Not only do we predominantly use open source technologies, our staff are major contributors to many notable open source projects. In other words, I think this is a really cool place to work. :-)

We're expanding our web application security practice and are looking to hire some experienced PHP developers who are interested in learning all about web …

After the two best years of my career to date, I will be leaving MySQL AB this coming Friday, July 14. Over that last two years as a member of the MySQL AB documentation team I have had the pleasure of working with some of the finest people I have ever met. MySQL is full of talented developers, trainers, writers, consultants, support people, etc. As a result, I have learned so much and will take so much away with me.

Why leave? The simple answer is that I have been made an offer I can’t refuse by a company called OmniTI. It offers new challenges, new work, and a new group of talented coworkers. I’ll be covering my new experiences in future posts.

Going forward, I still plan to be a vocal supporter of MySQL, continuing my efforts as a blogger and speaker. I will continue to submit MySQL-oriented sessions at conferences, and also plan to produce more articles and videos. MySQL has always had a great community and I’m looking …

After playing yesterday a bit with INSERT ... SELECT I decided to check is Innodb locks are relly as efficient in terms of low resource usage as they are advertised.

Lets start with a bit of background - in Innodb row level locks are implemented by having special lock table, located in the buffer pool where small record allocated for each hash and for each row locked on that page bit can be set. This in theory can give overhead as low as few bits per row, so lets see how it looks in practice:

I used the same sample table as yesterday:

PLAIN TEXT SQL:

1. CREATE TABLE `sample` (
2.   `i` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3.   `j` varchar(255) DEFAULT NULL,
4.   PRIMARY KEY  (`i`),
5.   KEY `j` (`j`)
6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
7.  
8. mysql> SHOW TABLE STATUS LIKE "sample" …

- PRESS RELEASE -

DAY ONE OF NEW EU PATENT WAR:
EU COMMISSION PUSHES FOR LITIGATION AGREEMENT

EU internal market commissioner McCreevy said at yesterday’s hearing
on the future of European patent policy in Brussels that he wants to
“move forward” with the European Patent Litigation Agreement (EPLA) -

Anti-software patent campaigners vehemently oppose the EPLA,
claiming it is “from a software patents point of view […] far worse”
than the directive they defeated in the European Parliament last year

Brussels (July 13, 2006) - At yesterday’s European Commission hearing in Brussels on the future of European patent policy (http://ec.europa.eu/internal_market/indprop/patent/hearing_en.htm), the EU’s internal market commissioner Charlie …