The latest from the MySQL community

Paul Tuckfield from YouTube is leading a session at the upcoming MySQL Conference & Expo April 23-26 on how they scale MySQL to deal with millions of videos from the world's most popular video web site.  YouTube has experienced the kind of exponential growth that every startup dreams of and Paul's practical tips and guidance will give you the benefit of their hard work and close collaboration with MySQL.

There are also other great practical sessions by top MySQL customers including Google, Nokia, FlickR, …

I leaned a new trick today with LOAD DATA INFILE . I’m migrating some data from an external source, and the Date Format is not the MySQL required YYYY-MM-DD, it was DD-MMM-YY.

Those that know me, know that I‘m a little paranoid about backup automation around the house (because I‘m lazy, and that if it wasn‘t automated, it wouldn‘t get done).

I‘m also a little paranoid about offsite backups, so therefore I have a VPN between my house and my parents‘ house, and send my nightly backups (via rsync) to their place as well.

Normally, when I check my e-mail in the morning, I see a little report about what got backed up. I didn‘t notice that it was missing this morning, and instead it arrived at 3:00 in the afternoon.

That seemed a little odd, so I went and looked at my cricket instance, and low-and-behold, the outbound rsync had the 768Kbps side of my DSL connection pegged since 02:00 AM. A little “du”ing around on my backup server, and I found out that a backup had taken place in the middle of a torrent of FC6. That wouldn‘t have been a problem, except that BT stores …

Those that know me, know that I'm a little paranoid about backup automation around the house (because I'm lazy, and that if it wasn't automated, it wouldn't get done).

I'm also a little paranoid about offsite backups, so therefore I have a VPN between my house and my parents' house, and send my nightly backups (via rsync) to their place as well.

Normally, when I check my e-mail in the morning, I see a little report about what got backed up. I didn't notice that it was missing this morning, and instead it arrived at 3:00 in the afternoon.

That seemed a little odd, so I went and looked at my cricket instance, and low-and-behold, the outbound rsync had the 768Kbps side of my DSL connection pegged since 02:00 AM. A little "du"ing around on my backup server, and I found out that a backup had taken place in the middle of a torrent of FC6. That wouldn't have been a problem, except that BT stores incomplete files in your …

Those that know me, know that I‘m a little paranoid about backup automation around the house (because I‘m lazy, and that if it wasn‘t automated, it wouldn‘t get done).

I‘m also a little paranoid about offsite backups, so therefore I have a VPN between my house and my parents‘ house, and send my nightly backups (via rsync) to their place as well.

Normally, when I check my e-mail in the morning, I see a little report about what got backed up. I didn‘t notice that it was missing this morning, and instead it arrived at 3:00 in the afternoon.

That seemed a little odd, so I went and looked at my cricket instance, and low-and-behold, the outbound rsync had the 768Kbps side of my DSL connection pegged since 02:00 AM. A little “du”ing around on my backup server, and I found out that a backup had taken place in the middle of a torrent of FC6. That wouldn‘t have been a problem, except that BT stores …

So far, this year, past and planned (as in currently reserved) travel means I will spend the equivalent of at least 21 work days inside aircraft - just for MySQL related travel.

eep.

Mark Radcliffe joins us this week to give his expert opinion on the latest draft of GPLv3. Mark is a friend and one of the industry's premier IP attorneys, especially with open source licensing questions. He is outside counsel for the OSI and chairs Committee C in the GPLv3 drafting process.

In other words, he knows his stuff.

Dave and I invited Mark to contribute to Open Sources on GPLv3. Here's his response:

The most recent draft of the GPLv3 was released on Wednesday, March 28. This guest blog will summarize the legal issues in the draft and some of the open issues. I have been involved in the process since the beginning because I am the chair of Committee C, the Users Committee, and I serve as outside general counsel on a pro bono basis for the Open Source Initiative. These comments are mine alone and do not represent the views of any of my clients.

The draft is part of a year long process of …

One very interesting thing I noticed with MySQL was that if you delete a database, ibdata file doesn’t shrink by that much space to minimize disk usage. I deleted the database and checked usage of /usr/local/mysql/var folder and noticed that ibdata file is still the same size. So the problem I face now [...]

Two open source funding announcements yesterday (Vyatta and Silicon Navigator) reminded me that I needed to also post the March funding announcements for Avidence and Penguin Computing.

Avidence: March 19th funding announcement (press release) of $5m in Series A by Red Rock Ventures and ArrowPath Venture Partners. Avidence is an early-stage startup in the process of developing an open source platform for digital video.

Penguin Computing: March 29th funding announcement (press release) of $9m in Series B, led by vSpring Capital, with participation from existing investors San Francisco Equity …

I guess many of you know, that using SELECT count(*) FROM table is problematic and slow when using Innodb tables.
This actually only applies to COUNT(*) queries without WHERE a clause as mentioned in the MySQL Performance Blog.

But if you got some slow count query in your application the best way to increase its performance is to replace / remove it.

So if you are going do to "SELECT count(*) FROM products" the best way, is to have a separated table
that stores the number of products. If you're inserting a row increment the counter, if you're deleting a row, decrement it.

Here is some example:
CREATE TABLE counter( number_of_products int(10) DEFAULT '0' NOT NULL);

Increment when you're adding a new product to the products table:

PLAIN TEXT SQL:

1. UPDATE counter SET …