Tagged articles

Optimizer_trace

19 articles · Page 1 of 1

Feb 23, 2026 · Databases

How to Diagnose and Optimize a Minute‑Long MySQL Join Query

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 AlgorithmsOptimizer_trace

0 likes · 13 min read

How to Diagnose and Optimize a Minute‑Long MySQL Join Query

Aikesheng Open Source Community

Feb 2, 2026 · Databases

Unlock MySQL’s Optimizer Trace: Reveal the ‘Why’ Behind Execution Plans

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.

EXPLAINOptimizer_traceTrace Variables

0 likes · 9 min read

Unlock MySQL’s Optimizer Trace: Reveal the ‘Why’ Behind Execution Plans

dbaplus Community

Apr 10, 2024 · Databases

How MySQL Chooses the Cheapest Index for COUNT(*) and When It Gets It Wrong

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 CostOptimizer_trace

0 likes · 9 min read

How MySQL Chooses the Cheapest Index for COUNT(*) and When It Gets It Wrong

Zhuanzhuan Tech

Mar 6, 2024 · Databases

Diagnosing and Resolving MySQL Optimizer Mis‑selection of Indexes

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 SelectionOptimizer_trace

0 likes · 11 min read

Diagnosing and Resolving MySQL Optimizer Mis‑selection of Indexes

dbaplus Community

Oct 22, 2023 · Databases

Why Does LIMIT + ORDER BY Yield Different Results in MySQL? Uncovering the Priority‑Queue Threshold

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.

LIMITOptimizer_traceheap sort

0 likes · 18 min read

Why Does LIMIT + ORDER BY Yield Different Results in MySQL? Uncovering the Priority‑Queue Threshold

Su San Talks Tech

Oct 17, 2023 · Databases

Why MySQL LIMIT + ORDER BY Returns Unexpected Rows – Priority Queue Threshold

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.

Optimizer_traceOrder ByQuery Optimization

0 likes · 19 min read

Why MySQL LIMIT + ORDER BY Returns Unexpected Rows – Priority Queue Threshold

macrozheng

Oct 13, 2023 · Backend Development

Why MySQL Sometimes Chooses Full Table Scans Over Indexes—and How to Diagnose It

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 OptimizationOptimizer_trace

0 likes · 11 min read

Why MySQL Sometimes Chooses Full Table Scans Over Indexes—and How to Diagnose It

Java Architect Essentials

Sep 15, 2023 · Databases

How MySQL Chooses Indexes: Cost Calculation and Optimization Techniques

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 OptimizationOptimizer_trace

0 likes · 10 min read

How MySQL Chooses Indexes: Cost Calculation and Optimization Techniques

Aikesheng Open Source Community

Jun 28, 2023 · Databases

Analyzing Slow UPDATE Locks in MySQL 5.7 and Optimizing with Indexes and Optimizer Trace

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.

InnoDBOptimizer_traceRR Isolation

0 likes · 8 min read

Analyzing Slow UPDATE Locks in MySQL 5.7 and Optimizing with Indexes and Optimizer Trace

Sohu Tech Products

May 4, 2023 · Databases

Comprehensive Guide to Analyzing and Optimizing Slow SQL Queries in MySQL

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/OOptimizer_trace

0 likes · 27 min read

Comprehensive Guide to Analyzing and Optimizing Slow SQL Queries in MySQL

Java High-Performance Architecture

Mar 15, 2023 · Databases

Why MySQL’s COUNT(*) May Use an Index and When It Chooses Full Table Scan

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.

EXPLAINOptimizer_traceSQL Performance

0 likes · 10 min read

Why MySQL’s COUNT(*) May Use an Index and When It Chooses Full Table Scan

ITPUB

Feb 21, 2023 · Databases

How MySQL Implements File Sorting: Internals, Modes, and Optimizations

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.

Optimizer_traceexternal sortingfilesort

0 likes · 32 min read

How MySQL Implements File Sorting: Internals, Modes, and Optimizations

Aikesheng Open Source Community

Dec 27, 2022 · Databases

Understanding How MySQL Converts Uncorrelated Subqueries to Correlated Subqueries and Their Execution Plans

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 PerformanceOptimizer_traceSubquery

0 likes · 15 min read

Understanding How MySQL Converts Uncorrelated Subqueries to Correlated Subqueries and Their Execution Plans

Liangxu Linux

Aug 15, 2022 · Databases

Why MySQL Picks a Full Table Scan Over an Index with ORDER BY id LIMIT 1

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.

Full Table ScanOptimizer_traceOrder By

0 likes · 8 min read

Why MySQL Picks a Full Table Scan Over an Index with ORDER BY id LIMIT 1

Java Backend Technology

Aug 1, 2022 · Databases

Why MySQL Picks Full Table Scan Over Index with ORDER BY … LIMIT 1 (and How to Fix It)

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.

Full Table ScanOptimizer_tracemysql

0 likes · 8 min read

Why MySQL Picks Full Table Scan Over Index with ORDER BY … LIMIT 1 (and How to Fix It)

Programmer DD

Apr 25, 2021 · Databases

Mastering MySQL: Proven Steps to Optimize Slow Queries

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.

Index TuningOptimizer_traceProfiling

0 likes · 10 min read

Mastering MySQL: Proven Steps to Optimize Slow Queries

Aikesheng Open Source Community

Apr 2, 2021 · Databases

Why MySQL Chooses a Composite Index Even When the Query Skips the Leftmost Column

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 IndexOptimizer_trace

0 likes · 3 min read

Why MySQL Chooses a Composite Index Even When the Query Skips the Leftmost Column

JavaEdge

Aug 30, 2020 · Databases

Master MySQL EXPLAIN: Decode Every Column and Boost Query Performance

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.

EXPLAINOptimizer_traceQuery Optimization

0 likes · 14 min read

Master MySQL EXPLAIN: Decode Every Column and Boost Query Performance

macrozheng

Jun 8, 2020 · Databases

Why MySQL’s Index Choice Can Miss the Best Plan – A Deep Cost Analysis

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 OptimizationOptimizer_trace

0 likes · 10 min read

Why MySQL’s Index Choice Can Miss the Best Plan – A Deep Cost Analysis