This article explains the purpose of MySQL's optimizer_switch prefer_ordering_index, shows why the default ON setting can hurt performance with skewed data, and demonstrates with code and EXPLAIN output how turning it OFF can lead to faster queries for ORDER BY and GROUP BY scenarios.
The article demonstrates through a practical experiment that MySQL will use indexes for an OR condition only when both operands are indexed, otherwise it falls back to a full table scan, and it suggests using UNION as an optimization when one side lacks an index.
This article examines frequent MySQL performance pitfalls—such as misuse of LIMIT, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, and condition push‑down failures—and demonstrates how rewriting queries with proper indexes, JOINs, early range reduction, and WITH statements can dramatically reduce execution time.
This article explains how MySQL implicitly converts mismatched column types—such as comparing a VARCHAR column with a numeric literal—into floating‑point numbers, causing seemingly equal values to match, losing index usage, and potentially producing incorrect query results.
An expert MySQL case study compares two similar queries—one using a materialized subquery and the other with an OR condition that triggers index reads—showing the former runs about 40 times slower, and explains the impact of materialization, semi‑join optimization, and index usage on performance.
