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

Background

MySQL includes an optimizer_switch option called prefer_ordering_index that is enabled (ON) by default. The setting influences how the optimizer handles queries that combine ORDER BY or GROUP BY with LIMIT, deciding whether to prefer an existing ordered index over a filesort operation.

Why Turning It Off May Be Better

The option can be detrimental when the data distribution is highly skewed and the chosen index does not provide selective access. In such cases the optimizer may scan the index row‑by‑row, perform a costly table‑lookup (random I/O), and still need to read almost all rows, making the plan slower than a simple full‑table scan with filesort.

Demonstration Setup

We create a sample users table and a stored procedure to populate 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>);

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 (CONCAT('user_', i), IF(RAND() > 0.5, 'M', 'F'), FLOOR(20 + (RAND() * 30)) );</code><code>        SET i = i + 1;</code><code>    END WHILE;</code><code>END //</code><code>DELIMITER ;

Effect of the Switch

Enable the option and run an EXPLAIN for a simple ordered query:

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

Result (using the ordered index):

+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------+</code><code>| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra |</code><code>+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------+</code><code>| 1  | SIMPLE      | users | NULL       | index | NULL          | idx_gender | 5       | NULL | 10   | 100.00   | NULL  |</code><code>+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------+

Disable the option and repeat the same query:

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

Result (full table scan with filesort):

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+</code><code>| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |</code><code>+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+</code><code>| 1  | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997227 | 100.00   | Using filesort |</code><code>+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+

The same pattern 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>| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows   | filtered | Extra       |</code><code>+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+</code><code>| 1  | SIMPLE      | users | NULL       | index | idx_gender    | idx_gender | 5       | NULL | 997227 | 100.00   | Using index |</code><code>+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+

Both ON and OFF produce the same plan for this particular GROUP BY because the index can satisfy the grouping.

Conclusion

Although prefer_ordering_index was introduced to help the optimizer choose an ordered index for ORDER BY / GROUP BY with LIMIT, in many real‑world scenarios the index scan incurs heavy random I/O and can be slower than a sequential scan with filesort. Consequently, most DBAs and developers disable this option to avoid unexpected performance regressions.