Build1 publisher3 min readPublished
TiDB quietly turns primary-key ORDER BY into a TopN, and the fix is not in v8.5.7
Mercari's DBRE team found that on tables with a composite or non-integer primary key, TiDB reads every matching index entry and then sorts. The upstream fix is in master, not in the current LTS.
The Engineer · Build desk
Drafted by a language model from the sources cited here and checked against its claim ledger before publication. How we use AISend a correction
What happened
- Mercari's DBRE (Database Reliability Engineering) team published the third and final of three articles introducing improvements it carried out through the end of March 2026.
- The central subject of the final article is handling the incompatibility in index behaviour between TiDB and MySQL.
- Condition for the divergence: tables with a clustered composite primary key or a non-integer primary key (common handle), when a non-unique secondary index is used.
- Symptom: ORDER BY on primary key columns is not recognised as the index order, and the plan becomes a TopN.
- Required response: when sorting by primary key order is needed, include the primary key columns at the tail of the index so the optimizer recognises the ordering, which makes behaviour equivalent to MySQL.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
Mercari's database reliability engineering team has published the third and final post in a series on the TiDB work it did through the end of March 2026, and the centrepiece is an index behaviour incompatibility with MySQL [1][2]. On a table whose clustered primary key is composite or non-integer, a query that filters through a non-unique secondary index and orders by the primary key does not get an ordered index read; the optimizer plans a TopN instead [3][4].
The reproduction in the upstream issue changes nothing but the primary key type [8]. Table `t1` has `PRIMARY KEY (a)` on an int column and `KEY ic (c)`; `t2` has `PRIMARY KEY (a1 varchar(64), a2 int)` and the same `KEY ic (c)` [8]. Both queries ask for the rows matching `c = 0` in primary key order, limit 100 [9]. For `t1`, TiDB plans an `IndexRangeScan` on `ic` with `keep order:true` and `estRows=100`, pushes the `Limit` into the coprocessor, embeds it in the `IndexLookUp`, and never emits a sort operator [10]. For `t2`, before the fix, the same scan reports `keep order:false` and `estRows=100100.60`, feeding a coprocessor `TopN` and a second `TopN` at the root [11]. That is roughly 1,001 times the estimated index entries read for the same 100 rows of output [22].
The mechanism is unremarkable once you see it. With `keep order:false` the optimizer has decided the index does not supply `(a1, a2)` order, so there is no basis for stopping the scan early, and every index entry matching `c = 0` is read before the sort [12]. Two `TopN` operators appear because coprocessor tasks run in parallel per region, producing per-region top-100 partial results that TiDB must merge [13]. The cost scales with the number of rows matching the `WHERE` clause, not with the `LIMIT`, so tables with a few hundred matching rows barely notice [14]. `t2` also pays a sort that `t1` does not, and because `a1` is `varchar` the comparisons are collation-aware [15].
This is a divergence, not a general property of clustered indexes. In MySQL, InnoDB secondary indexes are ordered `(c, a1, a2)` regardless of primary key type, the optimizer recognises that, and it seeks to `c = 0`, reads forward, and stops at 100 rows with no sort [16]. In TiDB, a single int primary key becomes an int handle and anything else becomes a common handle built from the encoded key columns, and the secondary index entries carry the primary key columns in both cases [17].
The remedy is to include the primary key columns at the tail of the index so the optimizer recognises the ordering [5]. Mercari says it already used ORDER BY-oriented indexes to some degree, and that this change was needed on a considerable number of tables [18]. It rates the work as relatively easy because DDL is online and fast, invisible indexes exist, and SQL plan management via bindings is available [19].
The version state is the operational point. Mercari went looking to contribute a fix and found it already merged in master as pingcap/tidb#66645, but the 8.5 backport, pingcap/tidb#67107, was unmerged at the time of writing and is absent from v8.5.7, the latest LTS [6][7].
Watch pingcap/tidb#67107 for the 8.5 landing. Until it merges there is nothing in the LTS line to wait for, so the trailing-PK index is the only lever [7][5]. Mercari's series also covers N+1 and hint-based plan control, which it frames as ordinary database problems that surface at the migration boundary [20].