Tagged articles
optimizer trace
21 articles · Page 1 of 1
This comprehensive guide walks through a complete MySQL performance optimization cycle: classifying three types of gradual slowdown, setting up slow logs and digest metrics, using EXPLAIN ANALYZE and optimizer trace to pinpoint a full-table scan on a 62M-row orders table, adding a composite index (user_id, status, created_at) via safe online DDL with three safety barriers, and establishing monitoring to prevent regression.
Composite IndexEXPLAINIndexing
0 likes · 47 min read

This article investigates whether SELECT COUNT(*) forces a full table scan, explains MySQL's cost‑based index selection (IO and CPU components), demonstrates the optimizer's choices with real‑world examples, and shows how EXPLAIN and optimizer trace can reveal and improve query performance.
EXPLAINIndex OptimizationMySQL
0 likes · 10 min read

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.
EXPLAINJoin AlgorithmsMySQL
0 likes · 13 min read

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.
EXPLAINMySQLSQL Optimization
0 likes · 9 min read

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.
COUNTIndex CostMySQL
0 likes · 9 min read

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.
EXPLAINIndex SelectionMySQL
0 likes · 11 min read

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.
LIMITMySQLheap sort
0 likes · 18 min read

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.
MySQLORDER BYheap sort
0 likes · 19 min read

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.
EXPLAINIndex OptimizationMySQL
0 likes · 11 min read

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.
EXPLAINIndex OptimizationMySQL
0 likes · 10 min read

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.
IndexInnoDBMySQL
0 likes · 8 min read

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.
EXPLAINI/OMySQL
0 likes · 27 min read

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.
EXPLAINMySQLSQL Performance
0 likes · 10 min read


ITPUBFeb 21, 2023 · Databases
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.
FilesortMySQLexternal sorting
0 likes · 32 min read

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.
Database PerformanceMySQLoptimizer trace
0 likes · 15 min read

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.
MySQLORDER BYOptimizer
0 likes · 8 min read

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.
MySQLfull table scanoptimizer trace
0 likes · 8 min read

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.
MySQLSQL Optimizationindex tuning
0 likes · 10 min read

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.
Composite IndexCovering IndexMySQL
0 likes · 3 min read

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.
EXPLAINMySQLSQL profiling
0 likes · 14 min read

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.
EXPLAINIndex OptimizationMySQL
0 likes · 10 min read
