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

MySQL provides the optimizer_switch variable prefer_ordering_index which, when enabled, makes the optimizer prefer an ordered index for queries that contain ORDER BY or GROUP BY together with LIMIT, potentially avoiding a filesort.

The default setting is ON, but in many cases this can degrade performance, especially when the indexed column has a highly skewed distribution (e.g., almost all rows have the same value). In such cases the optimizer may choose an index scan that reads almost every row, causing massive random‑IO from the index and subsequent row look‑ups, which is slower than a full table scan followed by a filesort.

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 ;

Running the same query with the switch ON and OFF shows the impact:

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

-- Result: type=index, key=idx_gender, rows=10 (index scan)

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

-- Result: type=ALL, Extra=Using filesort, rows≈997227 (full scan)

When the switch is ON, MySQL uses the idx_gender index, scanning it row by row and performing a costly lookup for each row. Because the query matches almost all rows, the random I/O overhead outweighs the benefit of the ordered index.

When the switch is OFF, MySQL performs a full table scan and then sorts the result. In the skewed‑data scenario this can be considerably faster than the index‑based plan.

Similar behavior is observed for GROUP BY queries:

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

-- Uses index, rows≈997227, Extra=Using index

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

-- Still uses index in this example, but the switch can affect plan choice in other cases.

In practice, many DBAs disable prefer_ordering_index because the optimizer does not yet have a reliable way to detect when the ordered‑index plan would be detrimental. Turning it off avoids the risk of inefficient index scans for queries with GROUP BY / ORDER BY on columns with low selectivity.