Optimizing LIMIT with ORDER BY and GROUP BY in MySQL 8.0 Using the prefer_ordering_index Parameter

When a LIMIT clause is used together with ORDER BY and GROUP BY, MySQL's optimizer often chooses to exploit existing index ordering, which may appear optimal but can lead to full table scans and poor performance on large datasets.

Since MySQL 8.0.21, the optimizer provides the prefer_ordering_index parameter that can be enabled or disabled via the optimizer_switch system variable.

SET optimizer_switch = "prefer_ordering_index=off";

SET optimizer_switch = "prefer_ordering_index=on";

In a test environment (MySQL Community Edition 8.0.30), a table t with a primary key id1 and a secondary index i(id2, c1) was created and populated with sample rows.

CREATE TABLE t (
  id1 BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  id2 BIGINT NOT NULL,
  c1 VARCHAR(50) NOT NULL,
  c2 VARCHAR(50) NOT NULL,
  INDEX i (id2, c1)
);

INSERT INTO t(id2,c1,c2) VALUES
  (1,'a','xfvs'),(2,'bbbb','xfvs'),(3,'cdddd','xfvs'),
  (4,'dfdf','xfvs'),(12,'bbbb','xfvs'),(23,'cdddd','xfvs'),
  (14,'dfdf','xfvs'),(11,'bbbb','xfvs'),(13,'cdddd','xfvs'),
  (44,'dfdf','xfvs'),(31,'bbbb','xfvs'),(33,'cdddd','xfvs'),
  (34,'dfdf','xfvs');

Default Parameter (ON)

mysql (test) > SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';
+------------------------------------------------------+
| @@optimizer_switch LIKE '%prefer_ordering_index=on%' |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+
1 row in set (0.00 sec)

With the parameter on, the optimizer chooses an index‑scan on the primary key id1 and then applies a WHERE filter on id2 , which can be inefficient for large tables.

EXPLAIN SELECT c2 FROM t WHERE id2>8 ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: i
key: PRIMARY
key_len: 8
ref: NULL
rows: 2
filtered: 69.23
Extra: Using where

Parameter Disabled (OFF)

SET optimizer_switch = "prefer_ordering_index=off";

EXPLAIN SELECT c2 FROM t WHERE id2>8 ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: range
possible_keys: i
key: i
key_len: 8
ref: NULL
rows: 9
filtered: 100.00
Extra: Using index condition; Using filesort

After turning the switch off, the optimizer uses the secondary index i to filter rows and applies the Index Condition Pushdown (ICP) feature, reducing physical I/O and avoiding a costly filesort on the primary key.

The experiment demonstrates that manually adjusting the prefer_ordering_index setting can lead to more efficient query plans, especially when the default optimizer choice is suboptimal.

Overall, MySQL’s optimizer has become increasingly intelligent across versions, but strategic parameter tuning remains valuable for achieving optimal performance.

have fun with MySQL 8.0 ^_^