1 views collected around this technical thread.
After upgrading a client from MySQL 5.7 to 8.0, query performance slowed dramatically due to changes in ORDER BY handling, and the article analyzes the root cause, presents verification tests, and recommends adding indexes to restore millisecond‑level response times.
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.
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.
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.
The article explains why using LIMIT together with ORDER BY in MySQL 5.6 can cause duplicate records on subsequent pages, analyzes the priority‑queue optimization that makes the sort unstable, and offers practical solutions such as indexing, proper pagination logic, and query adjustments.
The article explains the true execution order of SQL queries, detailing each step from FROM and JOIN through WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT, and illustrates the concepts with examples and code snippets.
Learn how to effectively sort query results using ORDER BY, apply ASC/DESC rules, combine multiple fields, use aliases, integrate functions, and limit result sets with LIMIT for pagination, while avoiding common pitfalls such as duplicate sorting values and expression misuse 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.
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.
This article explains why MySQL 5.6 may return duplicate rows on the second page when using ORDER BY with LIMIT, analyzes the optimizer’s priority‑queue behavior, and provides practical solutions such as indexing, proper pagination understanding, and handling of sorting edge cases.
This article explains why using ORDER BY with LIMIT for pagination in MySQL can yield inconsistent results when the ordering column contains duplicate values, describes MySQL's limit optimization behavior, and provides a reliable solution by adding a secondary unique sort key.
This article explains common misconceptions about index selectivity and filtering, demonstrates how composite indexes can avoid slow queries, and shows why order‑by may still trigger filesort, providing practical tips and SQL examples for better MySQL index design.
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.
A recent order‑fulfillment outage was traced to a MySQL query that used the primary‑key index because an ORDER BY id ASC LIMIT 350 forced a full scan, but tweaking the ORDER BY or removing it reduced execution time from 20 seconds to under one second.