Fun MySQL fact of the day: subquery materialization

Yesterday, we started looking at the semi-join strategies MySQL provides, and today, we're going to continue looking into semi-joins by considering the "materialisation" strategy.

If you haven't worked with MySQL too much, the materialisation strategy is the one that likely most closely aligns with your mental model of how non-correlated subqueries would work. Specifically, when MySQL decides to use a materialisation strategy to resolve a semi-join for a non-corelated subquery, MySQL will evaluate the subquery and store the results in an indexed temporary table. And from there on out, "everything is a join".

Let's once again consider yesterday's example relationship of company->employee. For clarity, we'll coax MySQL to choose a materialisation strategy and evaluate the output:

SET optimizer_switch='materialization=on,semijoin=off';
EXPLAIN SELECT * FROM employee WHERE company_id IN (SELECT id FROM company WHERE date_of_incorporation = 1996);

If you run this, you will see that the company table has a SUBQUERY select type, which we'd briefly discussed on Monday. This is in distinct contrast to MySQL 5.5's behaviour, which would have converted this query into a DEPENDENT SUBQUERY. And, like yesterday, if we SHOW WARNINGS, we can see the materialisation happening:

/* select#1 */ select `test`.`employee`.`id` AS `id`,`test`.`employee`.`company_id` AS `company_id` from `test`.`employee` where <in_optimizer>(`test`.`employee`.`company_id`,`test`.`employee`.`company_id` in (
    <materialize> (/* select#2 */ select `test`.`company`.`id` from `test`.`company` where (`test`.`company`.`date_of_incorporation` = 1996) ),
    <primary_index_lookup>(`test`.`employee`.`company_id` in <temporary table> on <auto_key> where ((`test`.`employee`.`company_id` = `materialized-subquery`.`id`)))))