Loading today’s stories
Build1 distinct publisher3 min readUpdated
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
Compiled by The EngineerSomething wrong?How this is made
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].
Follow any of these and your For You feed starts watching them — no settings page required.
Ranked by verification strength, evidence, and original report placement.
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.
While investigating in order to contribute a fix, Mercari found the behaviour had already been corrected in the latest master, as pingcap/tidb#66645.
Evidence-backed comparisons of source perspectives and observed adoption signals. Read the methodology
Which Builder, Operator, and Investor concerns the observed source mix emphasized—not a truth score.
Evidence, demonstrated adoption, hype gap, incentives, and confidence are assessed independently, each on its own current evidence. How these are measured.
Reproducible and specific, but single-source and self-reported
The account is unusually concrete for a vendor-independent blog: full DDL for the two comparison tables, both queries, side-by-side EXPLAIN output with operator IDs and estRows, an explanation of the two-stage TopN, the handle/clustered-index mechanism, and named upstream pull requests. That makes the core mechanism independently checkable by any reader with a TiDB instance. It is nonetheless one publisher describing its own migration: no third party in the cluster confirms the upstream merge states, and the impact figures shown are optimizer estimates rather than measured production cost.
One large operator's production remediation; no broader signal
There is real deployment evidence: a major marketplace ran a MySQL-to-TiDB migration and applied tail-primary-key indexes across a considerable number of production tables, plus N+1 and hint remediation, using online DDL, invisible indexes and SQL binding. But 'considerable number' is unquantified, and the cluster contains no data on how many other TiDB users are affected, no upstream release carrying the fix into LTS, and no third-party deployment reports, so the adoption footprint that can be evidenced remains one organisation.
Slightly understated relative to its own evidence
The source frames a plan regression in the current LTS in deliberately restrained terms: it foregrounds that the remediation is 'comparatively easy', that the cost difference is negligible where only a few hundred rows match, and that the behaviour is already fixed in master. It does not dramatise the roughly 1,001x gap between the two plans' estimated index reads, nor the fact that the LTS backport was still unmerged. There is no product pitch, benchmark leaderboard, or superlative claim to discount, so the narrative sits at or just below what its own plan output supports.
Practitioner post-mortem with employer-brand incentive, no vendor stake disclosed
The publisher is the operator, not the vendor: Mercari gains engineering-brand and recruiting value from a polished three-part migration series, and has a mild interest in portraying its own migration as competently handled, visible in the 'comparatively easy' framing and the note that it intended to contribute a fix upstream. Against that, it is publishing a compatibility defect in a product it depends on and pointing at an unmerged LTS backport, which cuts against pure promotion. The cluster discloses no commercial relationship with PingCAP, so any such incentive cannot be assessed from the supplied material.
Mechanism well supported; scope and current upstream state less so
Confidence is high on the technical mechanism, because the DDL, queries, plans and handle model are internally consistent and reproducible. It is materially lower on the two facts most likely to drive decisions: whether the 8.5 backport has since merged (a time-sensitive, single-source statement with no independent check in the cluster) and how many tables or queries any given fleet would need to change, which is described only qualitatively. Single-publisher coverage caps the ceiling.
build
A million-element IN clause MySQL absorbed took Mercari's TiDB cluster down with it1 distinct publisher
build
Mercari's TiDB gains came from storage knobs, while plan caching went back off1 distinct publisher
build
Slow Magento reindexes are a price index problem, and raw SQL makes it worse1 distinct publisher
build
Magento's inventory_reservation is a housekeeping bill that arrives as a hosting bill1 distinct publisher
Distinct publishers with included, body-backed reporting in this cluster.
1 article · August 20, 2026