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.
