When Should You Turn Off MySQL’s prefer_ordering_index? A Deep Dive

MySQL provides the optimizer_switch variable prefer_ordering_index to decide whether the optimizer should favor an ordered index for ORDER BY or GROUP BY queries that include a LIMIT. The default is ON, but many practitioners wonder whether it should be disabled.

The option is useful when the ordered index can satisfy the sort without extra filesort, but it becomes detrimental when the data distribution is highly uneven. If the indexed column (e.g., gender) contains the same value for the majority of rows, the optimizer will scan almost the whole index, then perform a costly random‑I/O “row‑by‑row” lookup (index‑scan + table‑lookup). This random I/O often exceeds the sequential I/O of a full‑table scan followed by a filesort, especially when the WHERE clause has low selectivity.

To illustrate, the article creates a users table and populates it with random gender and age values:

DROP TABLE IF EXISTS users;</code>
<code>CREATE TABLE users (</code>
<code>    id BIGINT AUTO_INCREMENT PRIMARY KEY,</code>
<code>    name VARCHAR(50),</code>
<code>    gender CHAR(1),  -- 'M' or 'F'</code>
<code>    age INT,</code>
<code>    INDEX idx_gender (gender)</code>
<code>);

A stored procedure load_users inserts a configurable number of rows with random genders and ages. After loading data, two sets of EXPLAIN statements are run:

SET optimizer_switch = 'prefer_ordering_index=on';</code>
<code>EXPLAIN SELECT * FROM users ORDER BY gender LIMIT 10;</code>
<code>SET optimizer_switch = 'prefer_ordering_index=off';</code>
<code>EXPLAIN SELECT * FROM users ORDER BY gender LIMIT 10;

With the option ON, the plan shows type = index and uses idx_gender, scanning many rows (e.g., 5 rows shown but actual rows estimated near the total). With OFF, the plan switches to type = ALL, performing a full table scan and a filesort, which in this skewed scenario is cheaper.

The same behavior appears for a GROUP BY query:

SET optimizer_switch = 'prefer_ordering_index=on';</code>
<code>EXPLAIN SELECT gender, COUNT(*) FROM users GROUP BY gender;</code>
<code>SET optimizer_switch = 'prefer_ordering_index=off';</code>
<code>EXPLAIN SELECT gender, COUNT(*) FROM users GROUP BY gender;

Both executions use the idx_gender index, but the cost of scanning the index outweighs the benefit of avoiding filesort when the index does not provide selective filtering.

The article includes a screenshot of the MySQL configuration options to illustrate where prefer_ordering_index appears:

In practice, most DBAs disable this switch because the optimizer cannot reliably predict when the ordered index will be beneficial, especially for queries where the indexed column has low selectivity. Until MySQL provides a smarter heuristic, turning the option off is the safer default for many workloads.