Why MySQL LIMIT with ORDER BY on Non‑Unique Columns Is Unstable and How to Fix It

Introduction

DBAs often need to paginate query results with ORDER BY and LIMIT. In MySQL 5.6, when the ordered column is not unique, the result set may be nondeterministic, which is a documented bug in the MySQL bug tracker.

Environment Setup

Create a test table without an index on the column a:

CREATE TABLE tb1 (
  id   BIGINT NOT NULL AUTO_INCREMENT,
  a    DECIMAL(19,2) NOT NULL,
  acid BIGINT NOT NULL,
  prid BIGINT NOT NULL,
  PRIMARY KEY (id),
  KEY idx_prid (prid),
  KEY idx_acid (acid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Data Initialization

Insert sample rows where the a values are not unique:

INSERT INTO tb1 (id, a, acid, prid) VALUES
(1, 2.00, 3, 2),
(2, 3.00, 3, 2),
(3, 4.00, 2, 3),
(4, 5.00, 2, 3),
(5, 6.00, 2, 3),
(6, 8.00, 2, 3),
(7,10.00, 2, 3),
(8,12.00, 2, 3),
(9,16.00, 2, 3),
(10,20.00,2,3),
(11,6.00, 2, 4),
(12,8.00, 2, 4),
(13,10.00,2,4),
(14,12.00,2,4),
(15,5.00,2,2),
(16,6.00,2,2);

Query Without Index on a

Run the pagination query: SELECT * FROM tb1 ORDER BY a DESC LIMIT 4; Typical output (order may vary):

+----+------+-------+------+
| id | a    | acid  | prid |
+----+------+-------+------+
|10 |20.00| 2     | 3    |
| 9 |16.00| 2     | 3    |
|14 |12.00| 2     | 4    |
| 8 |12.00| 2     | 3    |
+----+------+-------+------+
4 rows in set (0.00 sec)

Repeating the query with LIMIT 3 yields a different set of rows, demonstrating nondeterminism.

Adding an Index on a

Create an index on the non‑unique column: ALTER TABLE tb1 ADD KEY ind_tb1a (a); Run the same query again; the result set still varies, confirming that the presence of an index does not guarantee deterministic ordering when the column is not unique.

Solution: Add a Unique Secondary Sort Key

To obtain a stable order, append a unique column (e.g., id) to the ORDER BY clause:

SELECT * FROM tb1 ORDER BY a DESC, id DESC LIMIT 4;

The result set is now consistently:

+----+------+-------+------+
| id | a    | acid  | prid |
+----+------+-------+------+
|10 |20.00| 2     | 3    |
| 9 |16.00| 2     | 3    |
|14 |12.00| 2     | 4    |
| 8 |12.00| 2     | 3    |
+----+------+-------+------+
4 rows in set (0.00 sec)

Running the query with LIMIT 3 now consistently returns rows with id 10, 9, 14, matching the expected top‑three values of a.

Conclusion

When paginating results ordered by a non‑unique column, MySQL may return nondeterministic rows even if the column is indexed. The reliable fix is to include a unique column (such as the primary key) in the ORDER BY clause. This ensures a deterministic order, which is essential for applications that depend on stable pagination.