BestHub
Sign in
Tagged articles
40 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
Nov 24, 2024 · Databases

Boost MySQL Performance: Proven SQL Optimization Techniques

This article walks through practical MySQL performance tuning methods—including pagination, join, subquery, ORDER BY, GROUP BY, and COUNT optimizations—illustrated with real‑world data volumes, step‑by‑step SQL examples, EXPLAIN analyses, index creation, and measurable query‑time improvements.

JOINOrder BySQL Optimization
0 likes · 14 min read
Boost MySQL Performance: Proven SQL Optimization Techniques
Su San Talks Tech
Su San Talks Tech
Apr 15, 2024 · Databases

Mastering MySQL ORDER BY: Full-Field vs RowID Sorting and Optimizations

This article explains how MySQL executes ORDER BY queries, the role of sort_buffer, the difference between full-field and rowid sorting, how parameters like sort_buffer_size and max_length_for_sort_data affect performance, and how proper indexing can eliminate the need for sorting altogether.

Index OptimizationOrder ByRowid
0 likes · 12 min read
Mastering MySQL ORDER BY: Full-Field vs RowID Sorting and Optimizations
dbaplus Community
dbaplus Community
Dec 24, 2023 · Databases

Boost MySQL Performance: Proven SQL Optimization Techniques

When a system’s data grows, MySQL queries can become slow and cause latency; this article presents practical optimization strategies—including query tuning, pagination, join improvements, subquery replacement, order‑by and group‑by enhancements, and count() efficiency—illustrated with sample tables, code snippets, and performance screenshots.

GROUP BYJOINOrder By
0 likes · 13 min read
Boost MySQL Performance: Proven SQL Optimization Techniques
JD Retail Technology
JD Retail Technology
Oct 20, 2023 · Databases

Understanding Pagination Issues with ORDER BY and LIMIT in MySQL

This article examines why combining ORDER BY with LIMIT in MySQL pagination can produce duplicate rows on subsequent pages, analyzes the optimizer’s priority‑queue behavior introduced in version 5.6, and presents practical solutions such as using unique sort keys, adding ID ordering, and understanding deep‑pagination limitations.

LIMITOrder Bymysql
0 likes · 8 min read
Understanding Pagination Issues with ORDER BY and LIMIT in MySQL
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
Liangxu Linux
Liangxu Linux
Aug 27, 2023 · Databases

Understanding the Real Execution Order of SQL Queries

This article explains the true execution sequence of SQL statements—starting with FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT—illustrating each step with diagrams, examples, and code snippets to clarify how databases process queries.

FROMGROUP BYHAVING
0 likes · 6 min read
Understanding the Real Execution Order of SQL Queries
Architecture Digest
Architecture Digest
Feb 22, 2023 · Databases

Understanding the Execution Order of SQL Queries

This article explains the logical execution sequence of a typical SQL query, detailing each phase from FROM/JOIN/WHERE through GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT, and illustrating the process with diagrams and code examples.

From Join WhereGROUP BYHAVING
0 likes · 8 min read
Understanding the Execution Order of SQL Queries
Java Architect Essentials
Java Architect Essentials
Feb 19, 2023 · Databases

Understanding the Execution Order of SQL Queries

This article explains the step‑by‑step execution order of a standard SQL query, covering FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and LIMIT, and provides illustrative code snippets and diagrams to clarify each stage.

FROMGROUP BYHAVING
0 likes · 5 min read
Understanding the Execution Order of SQL Queries
ITPUB
ITPUB
Nov 12, 2022 · Databases

Understanding MySQL ORDER BY: Default vs Rowid Algorithms and Optimization Tips

This article explains how MySQL executes ORDER BY queries, compares the default sorting algorithm with the rowid method, and provides practical optimization steps such as indexing, adjusting sort_buffer settings, and choosing the appropriate algorithm based on data size and memory.

Order ByRowidSorting Algorithm
0 likes · 7 min read
Understanding MySQL ORDER BY: Default vs Rowid Algorithms and Optimization Tips
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
Senior Brother's Insights
Senior Brother's Insights
Oct 28, 2021 · Databases

Why MySQL LIMIT Can Return Duplicate Rows and How to Fix It

In a production environment, a pagination export feature caused duplicate and missing records due to MySQL’s nondeterministic ordering when multiple rows share the same ORDER BY value, and the article explains the root cause, official documentation, and a reliable solution using additional sorting columns.

LIMITOrder Byduplicate rows
0 likes · 10 min read
Why MySQL LIMIT Can Return Duplicate Rows and How to Fix It
Xiao Lou's Tech Notes
Xiao Lou's Tech Notes
Oct 12, 2021 · Databases

Optimizing ORDER BY/LIMIT in Sharded Databases with Cobar’s Algorithm

This article explains Cobar’s sharding middleware and presents an optimization technique for handling ORDER BY and LIMIT queries across multiple databases, detailing the original approach, its inefficiencies with deep pagination, the step‑by‑step improved algorithm, performance analysis, limitations, and practical applicability.

CobarLIMITOrder By
0 likes · 8 min read
Optimizing ORDER BY/LIMIT in Sharded Databases with Cobar’s Algorithm
ITPUB
ITPUB
Aug 19, 2021 · Databases

Why Does MySQL LIMIT with ORDER BY Return Duplicate Rows on Page 2?

When using MySQL pagination with LIMIT together with ORDER BY, the second page can unexpectedly contain rows from the first page because MySQL 5.6's priority‑queue optimization performs an unstable heap sort, causing nondeterministic ordering for equal values and leading to duplicate results.

LIMITOrder Bydatabase
0 likes · 7 min read
Why Does MySQL LIMIT with ORDER BY Return Duplicate Rows on Page 2?
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Aug 5, 2021 · Databases

Understanding and Optimizing ORDER BY in MySQL

This article explains the internal execution process of MySQL's ORDER BY, compares full‑field and rowid sorting, shows how indexes, sort_buffer size and max_length_for_sort_data affect performance, and provides practical optimization techniques such as composite and covering indexes.

Order ByRowidcovering index
0 likes · 13 min read
Understanding and Optimizing ORDER BY in MySQL
Laravel Tech Community
Laravel Tech Community
Jun 28, 2021 · Databases

Understanding and Optimizing MySQL ORDER BY Execution

This article explains how MySQL processes ORDER BY clauses, illustrates the execution steps with a full‑field index, discusses the impact of sort_buffer and max_length_for_sort_data, and shows how adding composite and covering indexes can eliminate filesort and improve query performance.

Index OptimizationOrder BySQL Performance
0 likes · 6 min read
Understanding and Optimizing MySQL ORDER BY Execution
JavaEdge
JavaEdge
Apr 13, 2021 · Databases

How MySQL Executes ORDER BY: Full-Field vs RowID Sorting and Index Optimization

This article explains MySQL's ORDER BY execution process, comparing full-field sorting with rowid sorting, detailing how sort_buffer, temporary files, and index usage affect performance, and showing how composite and covering indexes can eliminate sorting altogether.

Composite IndexIndex OptimizationOrder By
0 likes · 13 min read
How MySQL Executes ORDER BY: Full-Field vs RowID Sorting and Index Optimization
MaGe Linux Operations
MaGe Linux Operations
Jan 15, 2021 · Databases

Why ORDER BY with LIMIT Returns Unexpected Row Order in MySQL

When you combine ORDER BY with LIMIT in MySQL, the result set may appear in a different order than expected, especially if the ordered column contains duplicate values, and understanding the optimizer's behavior and adding deterministic columns can resolve this issue.

LIMITOrder Bydatabase
0 likes · 6 min read
Why ORDER BY with LIMIT Returns Unexpected Row Order in MySQL
Java Backend Technology
Java Backend Technology
Apr 23, 2020 · Databases

Why MySQL LIMIT Can Reverse ORDER BY in LEFT JOIN Queries

The author discovers a puzzling MySQL bug where moving the LIMIT clause outside a nested SELECT causes the final result set to be ordered incorrectly, explains how ORDER BY inside subqueries is ignored during LEFT JOIN processing, and recommends placing ORDER BY and LIMIT at the outermost query level.

JOINLIMITOrder By
0 likes · 5 min read
Why MySQL LIMIT Can Reverse ORDER BY in LEFT JOIN Queries
Tencent Cloud Developer
Tencent Cloud Developer
May 5, 2019 · Databases

Handling ORDER BY When Index Order Differs and Using Descending Indexes in MySQL

MySQL can avoid a filesort for ORDER BY clauses that match an index’s direction by scanning the index forward or backward, and MySQL 8.0’s descending indexes let mixed‑direction sorts be satisfied similarly; for older versions you can emulate this by retrieving rows in index order and reversing groups in the application.

Descending IndexOrder Bydatabase
0 likes · 9 min read
Handling ORDER BY When Index Order Differs and Using Descending Indexes in MySQL
ITPUB
ITPUB
Oct 25, 2017 · Databases

Mastering MySQL ORDER BY: Principles, Optimizations, and Index Pitfalls

This article explains how MySQL processes ORDER BY, compares conventional, optimized, and priority‑queue sorting algorithms, shows how to leverage indexes, and provides practical tuning tips such as adjusting max_length_for_sort_data and sort_buffer_size to avoid costly temporary files and random I/O.

Index OptimizationOrder BySorting Algorithms
0 likes · 12 min read
Mastering MySQL ORDER BY: Principles, Optimizations, and Index Pitfalls
ITPUB
ITPUB
Sep 7, 2017 · Databases

Master MySQL Index Usage: When and How Indexes Improve Queries

This guide explains how MySQL decides whether to use an index for range queries, LIKE patterns, and ORDER BY operations, detailing key_len calculation, index key vs filter vs table filter concepts, and practical examples for BETWEEN and sorting optimization.

BETWEENOrder Byindex
0 likes · 10 min read
Master MySQL Index Usage: When and How Indexes Improve Queries