OurSQL Episode 126: Subqueries and Tracking

This week we present part 2 of optimizer enhancements in MySQL 5.6, including subquery optimizations and the new optimizer trace feature. Ear Candy talks about what "hole punching" is with regards to file systems. In At the Movies we present "How to Deal with Difficult People".

Events
Galera, SkySQL and MariaDB are doing a Road Show in Stockholm on February 7th.

Oracle's doing more MySQL tech tours. These seminars will be in the mornings, and are free. They will be on:
Friday, February 15th near Milan
Tuesday, February 19th in Petach Tikva, Israel
Thursday, February 21st in Oslo and Brussels
Wednesday, March 20th in Stockholm.

LinuxFest Northwest will take place on Sat Apr 27th and Sun Apr 28th. The call for papers is open and you can submit proposals through Thursday Feb 28th.

Open Database Camp will be part of the Northeast LinuxFest at Harvard University, Cambridge MA Saturday March 16th and Sunday March 17th, 2013. You can register and submit presentations on the opensqlcamp.org website.

Percona Live: MySQL Conference and Expo 2013 is being held at the Santa Clara Convention Center Monday April 22nd - Thursday April 25th. Submit a lightning talk!

Training
SkySQL Trainings

Oracle's MySQL Trainings

Percona Trainings including Percona MySQL University in Montevideo and Buenos Aires

MySQL 5.6 Subquery Optimization
DBT3 benchmark

MySQL 5.6 new manual page on subquery optimization

Example:
So let's say you are using the sakila sample database and you want to figure out which customers have rented films. You would join the rental table with the customer table:
SELECT first_name, last_name FROM customer INNER JOIN rental USING (customer_id);
This repeats the customer name for each rental they have. If you run that query you'd get 16,044 rows returned, but only 599 different customers.

In MySQL 5.6 the optimal query is:
SELECT first_name,last_name FROM customer WHERE customerID IN (SELECT customerID FROM rental);

MySQL manual page on semi-joins

optimizer_switch feature

Blog post on IN() subqueries in MySQL 5.6 being optimized away

Oracle's MySQL blog about the new optimizer and replication features

Optimizer traces
optimizer traces, in the MySQL internals documentation.

Optimizer trace example

Ear Candy
We interview Tokutek's Bradley Kuszmaul, talking about what "hole punching" is with regards to file systems. We talked about file systems in episode 123.

At the Movies
This week in At the Movies we present Elizabeth Naramore's Dealing with Difficult People. Elizabeth presented this 2 months ago at the 2012 CodeConnexx Conference.

Where you can see us
Gerry will be at the February Seattle MySQL Meetup group on Monday February 11 2013.

Sheeri will be speaking at Rocky Mountain Oracle User Group Training Days in denver, colorodo from Monday Feb 11 - Thursday February 14th on "Backing up MySQL" and "Are You Getting the Most Out of Your Indexes?"

Sheeri will be speaking at SCALE 13 in Los Angeles from Friday, February 22nd through Sunday, February 24th on Are You Getting the Best Out of Your MySQL Indexes?

Sheeri will be speaking at Confoo in Montreal, Canada on Monday February 25 - Friday March 1, 2013, giving talks in English about "Are You Getting the Best Out of Your Indexes?" and "Different (MySQL) Forks for Different Folks".

Gerry will present a tutorial during the Percona Live: MySQL Conference and Expo from Monday, April 22 through Thursday April 25th. The tutorial is on Using TokuDB: A Guided Walk Through TokuDB Implementation. Many of the concepts apply to custom MySQL / MariaDB installations as well.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast