BestHub
Sign in
Tagged articles
19 articles
Page 1 of 1
dbaplus Community
dbaplus Community
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.

Join AlgorithmsOptimizer_traceOrder By
0 likes · 13 min read
How to Diagnose and Optimize a Minute‑Long MySQL Join Query
dbaplus Community
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.

Index CostOptimizer_trace_count
0 likes · 9 min read
How MySQL Chooses the Cheapest Index for COUNT(*) and When It Gets It Wrong
Zhuanzhuan Tech
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.

Index SelectionOptimizer_traceexplain
0 likes · 11 min read
Diagnosing and Resolving MySQL Optimizer Mis‑selection of Indexes
Su San Talks Tech
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 Byheap sort
0 likes · 19 min read
Why MySQL LIMIT + ORDER BY Returns Unexpected Rows – Priority Queue Threshold
Java High-Performance Architecture
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.

Optimizer_traceSQL Performancecost estimation
0 likes · 10 min read
Why MySQL’s COUNT(*) May Use an Index and When It Chooses Full Table Scan
ITPUB
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
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
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.

FORCE INDEXFull Table ScanOptimizer_trace
0 likes · 8 min read
Why MySQL Picks a Full Table Scan Over an Index with ORDER BY id LIMIT 1
Programmer DD
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
JavaEdge
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.

Optimizer_traceSQL profilingexplain
0 likes · 14 min read
Master MySQL EXPLAIN: Decode Every Column and Boost Query Performance
macrozheng
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.

Index OptimizationOptimizer_traceSQL Performance
0 likes · 10 min read
Why MySQL’s Index Choice Can Miss the Best Plan – A Deep Cost Analysis