Оптимизатор использует материализацию для повышения эффективности обработки подзапросов. Материализация ускоряет выполнение запроса, создавая результат подзапроса в виде временной таблицы, обычно в памяти. В первый раз, когда MySQL нуждается в результате подзапроса, он материализует этот результат в временную таблицу. В последующие разы, когда результат требуется, MySQL снова обращается к временной таблице. Оптимизатор может индексировать таблицу с помощью хэш-индекса, чтобы сделать поиск быстрым и недорогим. Индекс содержит уникальные значения, чтобы устранить дубликаты и сделать таблицу меньше.

Материализация подзапросов использует временную таблицу в памяти, если это возможно, переходя к хранению на диске, если таблица становится слишком большой. См. Раздел 8.4.4, «Использование внутренних временных таблиц в MySQL».

Если материализация не используется, оптимизатор иногда переписывает некоррелированный подзапрос как коррелированный подзапрос. Например, следующий IN подзапрос некоррелированный (where_condition включает только столбцы из t2, а не из t1):

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

Оптимизатор может переписать это как EXISTS коррелированный подзапрос:

SELECT * FROM t1
WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

Материализация подзапросов с использованием временной таблицы избегает таких переписываний и позволяет выполнить подзапрос только один раз, а не один раз на каждую строку внешнего запроса.

Для использования материализации подзапросов в MySQL должна быть включена системная переменная optimizer_switch с флагом materialization. (См. Раздел 8.9.2, «Переключаемые оптимизации».) При включенном флаге materialization материализация применяется к предикатам подзапросов, которые появляются где угодно (в списке выбора, WHERE, ON, GROUP BY, HAVING или ORDER BY), для предикатов, которые попадают под любой из этих случаев использования:

Следующие примеры иллюстрируют, как требование эквивалентности оценки предикатов UNKNOWN и FALSE влияет на возможность использования материализации подзапросов. Предположим, что where_condition включает только столбцы из t2, а не из t1, так что подзапрос некоррелированный.

Этот запрос подлежит материализации:

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

Здесь не имеет значения, возвращает ли предикат IN значение UNKNOWN или FALSE. В любом случае строка из t1 не включается в результат запроса.

Пример, где материализация подзапроса не используется, — это следующий запрос, где t2.b — столбец, допускающий значение NULL:

SELECT * FROM t1
WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2
                          WHERE where_condition);

Следующие ограничения применяются к использованию материализации подзапросов:

Использование EXPLAIN с запросом дает некоторое представление о том, использует ли оптимизатор материализацию подзапросов: