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

MySQL provides the optimizer_switch variable prefer_ordering_index (default ON) which influences how the optimizer handles ORDER BY or GROUP BY combined with LIMIT, deciding whether to use an existing ordered index instead of performing a filesort.

The switch is useful when the ordered index can satisfy the sorting without extra I/O, but it can be detrimental when the index does not provide selective scanning, causing the optimizer to perform an index scan and then a costly row‑by‑row lookup (random I/O) that outweighs a full table scan with filesort.

Typical problematic scenario: data distribution is highly skewed (e.g., most rows have the same value for a column such as gender), the index includes that column, and the query groups or orders by it. The optimizer will choose the index, scan almost all rows, and then perform many lookups, resulting in higher cost than a simple table scan.

Example schema and data‑loading procedure:

DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    gender CHAR(1), -- 'M' or 'F'
    age INT,
    INDEX idx_gender (gender)
);
DELIMITER //
CREATE PROCEDURE load_users(IN total INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= total DO
        INSERT INTO users(name, gender, age)
        VALUES (
            CONCAT('user_', i),
            IF(RAND() > 0.5, 'M', 'F'),
            FLOOR(20 + (RAND() * 30))
        );
        SET i = i + 1;
    END WHILE;
END//
DELIMITER ;

Toggle the switch and observe the execution plan:

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

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

When the switch is ON, the plan shows type = index and uses idx_gender, scanning the index and performing many lookups. When OFF, the plan shows type = ALL (full table scan) with Using filesort, which can be faster for the described data distribution.

Similar results appear for GROUP BY queries:

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

SET optimizer_switch = 'prefer_ordering_index=off';
EXPLAIN SELECT gender, COUNT(*) FROM users GROUP BY gender;

In both cases, the ON setting forces the optimizer to use the index, while OFF lets it fall back to a full scan, often yielding lower cost when the index is not selective.

Conclusion: Although prefer_ordering_index was introduced to improve certain ORDER BY/GROUP BY queries, in practice many workloads experience worse performance with it enabled. Until MySQL provides a more intelligent decision mechanism, most DBAs prefer to disable the option.