10 Real-World MySQL Subquery Optimizations to Boost Performance

This article explains why MySQL subqueries can hurt performance, outlines common pitfalls such as temporary tables, index loss, and optimizer complexity, and presents ten concrete examples that replace subqueries with IN, EXISTS, JOIN, indexes, temporary tables, window functions, and LIMIT to achieve faster, more maintainable queries.

Database TuningJOINSQL Performance

0 likes · 10 min read

10 Real-World MySQL Subquery Optimizations to Boost Performance

Aikesheng Open Source Community

Sep 21, 2020 · Databases

Understanding Semi‑join Materialization in MySQL Subquery Optimization

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.

Database PerformanceMaterializationSemi-Join

0 likes · 9 min read

Understanding Semi‑join Materialization in MySQL Subquery Optimization