Votes query timeout on space-scoped queries without a proposal filter (P2)

[tony8713]

Summary

PR #2237 fixes the dominant votes-query timeout pattern (P1: proposal-scoped, created-ordered queries — 99.7% of the killed queries). This issue tracks the residual pattern it does not cover: space-scoped votes queries with no proposal filter.

Mining BetterStack (source snapshot-hub-mainnet) for errno 3024 (max_statement_time kill) SQL over 14 days found 1,972 votes-query timeouts. After #2237, 5 of those (~0.25%) remain, all of this shape and all concentrated on arbitrumfoundation.eth.

This is rare but real production traffic, and the root cause is different enough from P1 that #2237's fix does not help it.

The pattern

The hub votes resolver builds its WHERE from the GraphQL where args. When the caller filters by space without a proposal, only space constrains the query, and the ordering is done over the whole space:

P2 — where: { space }, orderBy: vp DESC — 4 timeouts, all first page (LIMIT 0, 500 / LIMIT 0, 1000):

SELECT v.* FROM votes v
WHERE v.space = ?
ORDER BY v.vp DESC, v.id ASC
LIMIT ?, ?

P2b — where: { space }, orderBy: created ASC — 1 timeout, first page (LIMIT 0, 1000):

SELECT v.* FROM votes v
WHERE v.space = ?
ORDER BY v.created ASC, v.id ASC
LIMIT ?, ?

arbitrumfoundation.eth has ~9.1M votes. With no proposal to narrow the seek, both shapes either filesort or post-filter across the whole space, blow past max_statement_time, and get killed (errno 3024).

Evidence

• Source: BetterStack snapshot-hub-mainnet, killed-query SQL, 14 days.
• Volume: 5 of 1,972 votes timeouts (~0.25%), all arbitrumfoundation.eth, all first-page.
• Table: votes, ~65.6M rows total, ~9.1M for arbitrumfoundation.eth.

EXPLAIN on the read replica (arbitrumfoundation.eth), current indexes:

Query shape	key chosen	rows	Extra
P2 space + vp DESC (LIMIT 0,500)	idx_votes_on_space_proposal_created_id	~9,180,768	Using filesort
P2 same, FORCE INDEX (space_created_id)	space_created_id	~14,026,044	Using filesort (still)
P2b space + created ASC (LIMIT 0,1000)	idx_votes_on_created_id	51,486 (est.)	Using where (post-filters by space)
P2b same, FORCE INDEX (space_created_id)	space_created_id	56	none — clean seek, no filesort

The two cases are different: one is a hint problem, one is a missing index

The votes table currently has (from SHOW CREATE TABLE):

KEY `vp` (`vp`)
KEY `idx_votes_on_vp_value` (`vp_value`)
KEY `space_created_id` (`space`,`created`,`id`)
KEY `idx_votes_on_space_created_desc_id` (`space`,`created` DESC,`id`)
KEY `idx_votes_on_space_proposal_created_id` (`space`,`proposal`,`created`,`id`)
KEY `idx_votes_on_created_id` (`created`,`id`)
KEY `idx_votes_on_proposal_vp_id` (`proposal`,`vp`,`id`)

P2b (created ASC) is a planner/hint problem, not a missing index. space_created_id (space, created, id) — all ascending — already satisfies WHERE space = ? ORDER BY created ASC, id ASC as a pure ref seek with no filesort (EXPLAIN above: rows 56, no Extra). The optimizer just doesn't pick it: left alone it chooses idx_votes_on_created_id (created, id), scans in global created order, and post-filters by space — the same wrong-index anti-pattern #2237 solved for P1. A targeted FORCE INDEX (space_created_id) on the space-only, created-ordered path fixes it, directly analogous to #2237.

P2 (vp DESC) is a genuinely missing index. There is no (space, vp, …) or (space, vp_value, …) composite. The vp / vp_value indexes lead with the value, not space, so the planner can either seek space and filesort the whole ~9.1M-row partition by vp (what it does today), or scan a vp index and post-filter space. Even forcing the best space-leading index still filesorts (EXPLAIN above). So P2 cannot be fixed by a hint alone — it needs either a new index or a different pagination strategy.

Suggested fix directions (needs investigation — not prescriptive)

• P2b: apply a targeted FORCE INDEX (space_created_id) (and match the id tie-break direction to the created direction, as fix(hub): use composite index for proposal-scoped votes query #2237 did) on the space-scoped, created-ordered path. The covering index already exists; this is purely steering the planner onto it.
• P2: add a composite index to support space-scoped vp ordering — (space, vp, id) (or (space, vp_value, id) if the resolver orders by vp_value). Confirm which column the orderBy: vp path actually emits before choosing. This is a genuine schema add, so weigh write/space cost against 4 timeouts/14d.
• Both: keyset / cursor pagination (WHERE space = ? AND (vp, id) < (:cursor…) ORDER BY … LIMIT n) would remove the deep-offset cost too, matching the follow-up already noted in fix(hub): use composite index for proposal-scoped votes query #2237. Larger change.

Given the volume (~0.25%, one space), this is low urgency, but the P2b half is nearly free (reuses an existing index + the #2237 pattern) and the P2 half is where the real design decision is.

Reference: #2237 for the analogous P1 fix and the EXPLAIN methodology.

---

Related: proposal-scoped vp DESC ordering also filesorts on very large proposals (latent, not yet timing out)

The regression testing on #2237 surfaced a third vp-ordering gap that belongs in this issue so the whole "vp ordering without a good index" problem lives in one place.

where: { proposal } with orderBy: vp DESC — the UI's default vote sort — still does a filesort on both master and the #2237 branch for very large proposals. #2237 only fixes the created-ordered proposal path; its FORCE INDEX gate deliberately excludes vp, so this shape is untouched by that PR.

Caveat — this is latent, not a live incident. It did not appear in the errno-3024 timeout mining (0 timeouts over 14 days for this shape). It is a filesort-risk on the largest proposals, not a query that is being killed today. Do not over-prioritize it against the P2 space-scoped case, which is backed by actual production timeouts.

Finding: the proposal+vp index already exists — the filesort is a tie-break direction mismatch, not a missing index

votes has KEY idx_votes_on_proposal_vp_id (proposal, vp, id) — all columns ascending. The planner already picks this index for the proposal+vp path (no hint needed), but whether it filesorts depends entirely on the id tie-break direction:

EXPLAIN on the read replica, stgdao.eth proposal 0xc18c…5920 ("Deprecate the WOO liquidity pool on Fantom", ~514k votes; the index ref matches ~1.03M rows):

Query shape	key chosen	rows	Extra
proposal = ? + ORDER BY vp DESC, id ASC (the resolver's actual shape, LIMIT 0,500)	idx_votes_on_proposal_vp_id	~1,035,320	Using filesort
proposal = ? + ORDER BY vp DESC, id DESC (tie-break direction matched to vp)	idx_votes_on_proposal_vp_id	~1,035,320	Backward index scan — no filesort

The index fully covers proposal + vp DESC ordering; MySQL just can't serve vp DESC, id ASC from an all-ascending index without sorting, because that mixes scan directions (it needs either forward vp ASC, id ASC or backward vp DESC, id DESC). The resolver emits vp DESC, id ASC, so it hits the filesort path and reads/sorts the whole ~1M-row proposal partition. Matching the tie-break to the vp direction (id DESC) turns it into a bounded backward index scan.

So, to be precise about which of the three it is:

• Not a missing index — the composite exists and covers the ordering.
• Not a plain index-selection hint problem (unlike P2b) — the planner already chooses the right index.
• It's a tie-break-direction problem: the id ASC secondary sort doesn't match vp DESC, forcing the filesort. Fixable by aligning the tie-break direction (analogous to how fix(hub): use composite index for proposal-scoped votes query #2237 matched the id direction to the created direction), without a new index. Note that flipping the tie-break to id DESC changes the ordering among equal-vp rows, so it needs to be applied consistently with pagination — a product/keyset decision, not purely mechanical.

Unified fix direction (needs investigation, not prescriptive)

The vp-ordering index gap now spans two distinct scopes with two different remedies:

• Space-scoped (P2, above): genuinely missing a (space, vp, id) composite — needs a schema add or keyset pagination.
• Proposal-scoped on huge proposals (this section): the covering composite already exists; only the tie-break direction forces the filesort — a resolver-side direction fix, no new index.

A coherent, single fix direction: ensure every vp-ordered query has a covering composite in its scoping dimension and emits a tie-break whose direction matches the vp direction (so the composite can be scanned as an ordered index instead of filesorted), ideally converging on keyset/cursor pagination (WHERE scope = ? AND (vp, id) < (:cursor) ORDER BY vp DESC, id DESC LIMIT n) which removes both the filesort and the deep-offset cost at once. This is the same follow-up already noted in #2237.

Replica evidence gathered read-only (SHOW CREATE TABLE + EXPLAIN only); no schema or data changed.