The article explains why placing column C immediately after the equality column A in a composite index (A, C, B) avoids filesort and keeps performance stable regardless of how many values appear in the B IN list, outperforming other index orders such as (A, B, C).
The article explains why MySQL may still report Using filesort despite a composite index that appears to satisfy both WHERE and ORDER BY clauses, covering index ordering rules, the impact of range conditions, mismatched ORDER BY field order, ASC/DESC mixing, and practical optimization techniques.
This article examines MySQL’s optimizer_switch ‘prefer_ordering_index’ setting, explains how it influences ORDER BY/GROUP BY queries with LIMIT, demonstrates cases where keeping it ON harms performance due to costly index scans, and provides concrete SQL examples and recommendations to turn it off in most scenarios.
This article explains the purpose of MySQL's optimizer_switch prefer_ordering_index, why the default ON can hurt performance on skewed data, and demonstrates with table creation, data‑loading procedures, and EXPLAIN output how turning the option OFF often yields faster queries.
This article explains MySQL's sorting mechanisms, comparing index sorting and filesort, detailing memory and disk phases, single‑row and double‑row strategies, and provides practical optimization tips such as proper index design and buffer configuration.
This article explains the most frequent MySQL EXPLAIN EXTRA hints—Using filesort, Using temporary, and Using join buffer—detailing when they appear, how to interpret them, and practical optimization strategies illustrated with concrete SQL examples and execution‑plan outputs.
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.
This article provides an in‑depth analysis of MySQL's filesort mechanism, explaining the original and modified algorithms, how sort and addon fields are calculated, memory allocation strategies, the role of temporary files, and practical tips to reduce excessive disk usage during sorting operations.
This article explains why MySQL ORDER BY may trigger a filesort when no suitable index exists, demonstrates the issue with a product query, and shows how adding a composite index on category_id and price lets the database return rows already sorted, removing the need for an extra sorting step.
