Should You Disable MySQL’s prefer_ordering_index? A Practical Performance Guide

MySQL includes the optimizer switch prefer_ordering_index, which is enabled (ON) by default. It tells the optimizer to prefer using an existing ordered index for queries that contain ORDER BY or GROUP BY together with LIMIT, aiming to avoid a filesort operation.

The option can be detrimental when the data distribution is highly skewed. If the chosen index does not provide a selective scan—e.g., an index on a column like gender where almost all rows share the same value—the optimizer will perform an index scan followed by many random row look‑ups, resulting in far higher I/O than a simple full‑table scan with filesort.

Example schema and data load

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>);</code>
<code>DELIMITER //</code>
<code>CREATE PROCEDURE load_users(IN total INT)</code>
<code>BEGIN</code>
<code>    DECLARE i INT DEFAULT 1;</code>
<code>    WHILE i <= total DO</code>
<code>        INSERT INTO users(name, gender, age)</code>
<code>        VALUES (</code>
<code>            CONCAT('user_', i),</code>
<code>            IF(RAND() > 0.5, 'M', 'F'),</code>
<code>            FLOOR(20 + (RAND() * 30))</code>
<code>        );</code>
<code>        SET i = i + 1;</code>
<code>    END WHILE;</code>
<code>END //</code>
<code>DELIMITER ;

After populating the table, the effect of the switch can be observed with EXPLAIN:

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

Result (type = index, using idx_gender, rows ≈ 5). The optimizer chooses the ordered index.

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

Result (type = ALL, using filesort, rows ≈ 997 227). The optimizer falls back to a full‑table scan.

When the query scans almost all rows (e.g., 99 % of the table), the index‑based plan incurs massive random I/O due to row‑by‑row look‑ups, while the filesort plan performs sequential I/O, which is usually faster.

The same switch can be tested with a GROUP BY query:

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

Both the ON and OFF settings use the idx_gender index, showing that the switch does not affect this particular query.

Conclusion

Although the prefer_ordering_index option was introduced to help the optimizer avoid costly filesorts, in practice it can lead to inefficient index scans when the indexed column is not selective. Because MySQL currently provides no automatic way to detect such cases, most DBAs disable the option (set it to OFF) to avoid unpredictable performance regressions, especially for queries that combine GROUP BY / ORDER BY with LIMIT.