This article explains how to read MySQL's EXPLAIN EXTRA column, covering SEMI JOIN hints such as FirstMatch, Start/End temporary, LooseScan, and the Range‑checked‑for‑each‑record optimization, with concrete examples and command‑line output to illustrate each case.
A large‑scale user‑push system suffers from minutes‑long MySQL queries because the IN subquery materializes a temporary table and triggers a semi‑join that forces full table scans, but disabling the semi‑join optimizer or rewriting the query restores sub‑second performance.
This article examines why a MySQL query that uses an IN subquery on a massive users table becomes extremely slow, analyzes the execution plan revealing materialized temporary tables and semi‑join optimization, and demonstrates how disabling the optimizer or rewriting the query restores index usage and dramatically improves performance.
The article analyzes a slow MySQL query that uses an IN subquery on a massive user table, explains how the optimizer’s semi‑join and materialized temporary tables cause full‑table scans, and demonstrates practical fixes such as disabling the semi‑join optimizer switch or rewriting the SQL to regain index usage.
This article explains how MySQL transforms eligible subqueries into semi‑joins, describes the four semi‑join strategies, and details the Semi‑join Materialization approach—including Materialization‑scan and Materialization‑lookup—while showing how to identify the chosen strategy via EXPLAIN output.
An expert MySQL case study compares two similar queries—one using a materialized subquery and the other with an OR condition that triggers index reads—showing the former runs about 40 times slower, and explains the impact of materialization, semi‑join optimization, and index usage on performance.
