This article walks through a real‑world MySQL slow‑query case, explains the inner workings of Join and Order by algorithms, shows how to interpret EXPLAIN and Optimizer_trace output, and demonstrates how adding an index can transform a costly Block Nested‑Loop Join into a fast Index Nested‑Loop Join, dramatically improving performance.
This article explains MySQL’s Optimizer Trace feature, covering its purpose, required system variables, how to enable it, the structure of its JSON output, and how to interpret the preparation, optimization, and execution stages to diagnose query‑plan decisions.
This article examines whether SELECT COUNT(*) causes full‑table scans, explains MySQL’s optimizer cost‑based index selection (including IO and CPU costs), demonstrates with a 100k‑row table how auxiliary indexes are chosen, and shows cases where the optimizer’s estimates mislead performance.
This article recounts a real‑world incident where MySQL 5.7 chose a sub‑optimal index, causing a three‑second slow query, and explains how the team identified the root cause, used EXPLAIN and optimizer trace, and applied fixes such as forced index hints and data cleanup.
This article reproduces MySQL's official LIMIT‑optimization example, shows how adding rows changes the ORDER BY + LIMIT result, explains the hidden priority‑queue mechanism revealed by optimizer_trace, and derives the exact row‑count threshold where the behavior switches.
This article investigates the nondeterministic behavior of MySQL when combining ORDER BY with LIMIT, reproduces the official example, identifies a critical row count threshold where the optimizer switches to a priority‑queue (heap) sort, and explains the underlying source‑code logic and its impact on query results.
This article explains how MySQL's optimizer calculates execution costs for different indexes, why it may select a full table scan instead of an apparently better index, and shows how to use EXPLAIN and optimizer trace to reveal and improve the chosen execution plan.
This article explains how MySQL calculates execution costs for different indexes using IO and CPU metrics, demonstrates with a sample table and queries, compares the optimizer's choices with actual performance, and shows how to use EXPLAIN and optimizer trace to fine‑tune query plans.
The article investigates why an UPDATE statement on a MySQL 5.7 InnoDB table with RR isolation experiences high lock time, demonstrates how missing indexes cause full‑table scans and blocking, and shows how adding an index and using optimizer trace can diagnose and resolve the issue.
This article explains why SQL queries become slow, how to capture slow queries using MySQL's slow query log, and provides detailed step‑by‑step methods for analyzing execution plans, profiling, optimizer tracing, and I/O parameters to identify and resolve performance bottlenecks.
This article examines how MySQL decides whether to use an index or perform a full table scan for COUNT(*) and other queries, explains the IO and CPU cost model, demonstrates the calculations with a sample table, and shows how optimizer trace can reveal mismatches between estimated and actual performance.
This article explains MySQL's file‑sort mechanism in depth, covering the sort buffer, handling of long sort keys, three sort modes, priority‑queue and read‑rnd‑buffer optimizations, internal vs. external sorting, descending sort implementation, and how to inspect details with optimizer trace.
This article explains the process by which MySQL transforms uncorrelated IN subqueries into correlated subqueries, details the differences in EXPLAIN output columns, explores optimizer trace information, compares materialization versus correlated execution costs, and provides best‑practice recommendations for writing efficient queries.
A MySQL 5.6+ optimizer bug causes queries that filter by uid and order by id with a small LIMIT to use a costly full table scan instead of the appropriate idx_uid_stat index, and the article explains the root cause, shows optimizer_trace output, and offers two practical work‑arounds.
The article explains why MySQL sometimes chooses a full table scan instead of using an available index when a query includes ORDER BY id ASC LIMIT 1, demonstrates the optimizer's cost calculations with optimizer_trace, and offers two practical work‑arounds to force the optimal index usage.
This article outlines a systematic approach to identifying and fixing inefficient MySQL queries, covering slow‑query detection, EXPLAIN analysis, profiling, optimizer tracing, and practical case studies that demonstrate index tuning, query rewriting, and handling large‑scale pagination and complex conditions.
This article explains why MySQL may still use a composite index as a covering or range index despite the query not following the leftmost‑prefix rule, by analyzing optimizer trace output and demonstrating the behavior with concrete experiments.
This guide explains each column returned by MySQL's EXPLAIN output, details join types, possible keys, extra information, and shows how to use EXPLAIN EXTENDED, SHOW PROFILE, and optimizer trace to analyze and optimize SQL queries effectively.
This article examines how MySQL estimates execution costs for queries, explains why COUNT(*) can be optimal, demonstrates index selection with real‑world examples, and shows how optimizer_trace can reveal mismatches between estimated and actual performance.
