Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn't matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you've seen it, it's really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I'd capture it here in case it's helpful to anyone else.

Consider that tried-and-tested example: employees and managers. Here's the staff table from the database (today's imaginary data isn't particularly imaginative, sorry):

mysql> select * from staff;
+----+------------+-----------+------------+
| id | first_name | last_name | manager_id |
+----+------------+-----------+------------+
|  1 | Hattie     | Hopkins   |          4 |
|  2 | Henry      | Hopkins   |          4 |
|  3 | Harry      | …

Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn’t matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you’ve seen it, it’s really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I’d capture it here in case it’s helpful to anyone else.

Consider that tried-and-tested example: employees and managers. Here’s the staff table from the database (today’s imaginary data isn’t particularly imaginative, sorry):

mysql> select * from staff;
+----+------------+-----------+------------+
| id | first_name | last_name | manager_id |
+----+------------+-----------+------------+
|  1 | Hattie     | Hopkins   |          4 |
|  2 | Henry      | Hopkins   |          4 |
|  3 | …

Here’s your spammy day-of reminder about tonight’s free MySQL meetup. The topic is MySQL Monitoring Bonanza. There will be beer, pizza, pretty charts and graphs, and friends. Free as in beer (and pizza, did I mention that?)!

Further Reading:

• New Central Virginia MySQL Meetup
• Free webinar on monitoring MySQL
• Speaking at MySQL Meetup in Northern Virginia
• …

If you follow what Percona is up to, you might have noticed that I’ve created a set of high-quality monitoring and graphing plugins for MySQL and related systems. Currently they support Nagios and Cacti. I’ll give a free webinar on March 28th discussing these, and more broadly, discussing how to monitor MySQL successfully to avoid common problems like spammy alerts about nonexistent problems.

Further Reading:

• Free webinar on preventing MySQL downtime
• Free webinar on MySQL performance this Thursday
• …

My earlier blog post was wrong, our block of rooms for the conference is still available past the 12th of March. However, there are apparently zero rooms available in the Hyatt other than our reserved block, and our block discount will expire soon (I’m a little hazy on the exact details, because I thought it was the 12th). What I have been told in no uncertain terms is this: “If you know anyone who plans to attend the conference and needs a hotel room, they should book IMMEDIATELY in our room block or they will not be able to get a room in the Hyatt either at a reasonable rate or at all.“

Please don’t ask me to help — I’m not involved in this, just trying to get the information out there. The logistics are beyond my ken.

Update: apparently the room discount rate ends on the 19th when our reservation on the block of rooms …

I’ll speak at the Shenandoah Ruby Users Group meeting next week in Harrisonburg, Virgina. I’m preparing a brand-new talk for this, called “Seven Things To Know About MySQL Performance.” Hopefully my copies of the new High Performance MySQL, 3rd Edition will have arrived by then and I can give one away!

The meetup is free and friendly. If you’re in the area, you oughta be there!

Further Reading:

• New Ruby Conference: Ruby on Ales 2011
• Speaking at the Southern Computer Measurement Group …

I was trying to parse the data from the common platform enumeration which is a big dictionary of all or most of commercially or freely available applications, operating systems and hardware (the hardware part I didn't really understand).

Here is an example for "Microsoft Access 2000 sp2"
cpe:/a:microsoft:access:2000:sp2

As you can see, the data is separated by colons.
To get the parts that I want in that string, I used the following SQL statement:

*edit from Scot's comment*
mysql> set @v="cpe:/a:microsoft:access:2000:sp2";
Query OK, 0 rows affected (0.00 sec)mysql> set @p=2 /*the part I want */;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@v,':',@p),':',-1);
+---------------------------------------------------------------------+
| SELECT …

We’re running Birds of a Feather (BoF) sessions and lightning talks this year at the MySQL conference, similarly to previous years. You can help by submitting proposals ASAP. The deadline is Monday, and it is a hard deadline.

Submit a BoF Session • Submit a Lightning Talk

Why is this a big deal? Why the hard deadline? It turns out that this is one of the things I always underestimated. Planning for these is actually quite difficult and they are very expensive. That’s because we have to ensure that there is room for them to take place, doors are unlocked, and the lights are turned on. This really surprised me — I always thought it was simple to let people gather together in the evening. Well, nothing is simple at …

The third edition is now available in ePub, Mobi, and PDF formats. Print format should be available early next week.

Further Reading:

• High Performance MySQL, Third Edition
• High Performance MySQL, Second Edition: Replication, Scaling and High Availability
• High Performance MySQL 2nd Edition gets revised and translated
• …

I presented at this year’s Hotsos Symposium. I am searching for a claim to specialness, and I think it may be that I am the first Hotsos presenter who’s specifically focused on MySQL. True? I don’t know, but I’ll run with it for now.

My topic was on extracting black-box performance metrics from TCP packet headers and timestamps and finding hidden performance problems in the system, without any knowledge of what the client and server are talking to each other about. I then extended the same data to performance and scalability modeling, which you can use for purposes such as forecasting, capacity planning, and bottleneck analysis.

This technique works on MySQL because its TCP protocol is half-duplex, and it’ll work for any system with a half-duplex protocol. Does it work on Oracle Database? I am not sure, and no one else I’ve spoken to yet has been …