Hacktoberfest 2026：维护者为十月标记出来的 issue，仍然开放、适合新手。 浏览 Hacktoberfest issue

Slow queries in replay endpoints

未关闭
  • 哪里不对？
#1,182 3 条评论 0 个 reaction 已指派 0 人 在 GitHub 查看

还没有人认领这个 Issue。

评估

难度
4/5
预计耗时
3-5 天
新手友好度
48/100
Issue 类型
缺陷
描述清晰度
基本清楚
活跃度
活跃
技术栈
java, spring, sql
领域
backend-api-design, databases

调研方向

Start with Game.java and the referenced V107__baseline_schema.sql indexes, then inspect later migrations in FAForever/db and the query plan for the map-filtered, newest-first /data/game request. Check whether the proposed composite index is appropriate for this query and how Elide uses it. Done means the change is measured against the reported slow search and its impact on other queries is considered.

由索引模型根据 Issue 内容生成。

描述

Searching games by map is slow for popular maps

Searching /data/game with a filter on the map name takes up to 20 seconds when the map is popular. The same search without the sort, or with a player filter added, answers in under 0.2 s. Claude says this may be because of a missing index. This would be a small change, but I can't oversee the exact implications myself. Essentially this is suggested:

A composite index in FAForever/db:

CREATE INDEX game_stats_mapId_startTime ON game_stats (mapId, startTime);

I've always noticed that the replay tab can be quite slow, this has been happening for years. I only make this ticket because I noticed that it may be a small fix. But I don't know how Elide interacts with an additional index. If I am totally off based on your gut, then feel free to close this immediately.

The query

The web vault (https://vault.jipwijnia.nl) searches games like this, 12 per page, newest first:

GET /data/game
  ?include=playerStats,playerStats.player,mapVersion,mapVersion.map,featuredMod
  &sort=-startTime
  &page[size]=12&page[number]=7
  &page[totals]
  &filter=endTime=isnull=false;mapVersion.map.displayName=="*Seton*"
Measurements

Measured on 2026-10-06 from a browser, with a bearer token. The API caches answers per URL (repeating a URL returns in about 55 ms), so each search was run three times with a different page (7, 8 and 9) to make every call uncached. The three runs were within 2% of each other.

Search (all with endTime=isnull=false) Matching games Time
No other filter 19.2 million 1.25 s
No other filter, no page[totals] 0.08 s
Player (a player with about 5,000 games) 5,087 0.22 s
Map *Seton* 1.35 million 20.8 s
Map *Seton*, no page[totals] 19.1 s
Map *Seton*, no page[totals], no include 19.0 s
Map *Seton*, no page[totals], no sort 0.09 s
Map "Seton's Clutch" (exact), no page[totals] 14.8 s
Map *Fields of Isis*, no page[totals] 4.6 s
Map *Theta Passage*, no page[totals] 1.1 s
Player and map *Seton* 834 0.14 s

The moment I search for a map with many replays + sorting the endpoint time explodes.

Suspected cause

Game maps to game_stats and reaches the map through mapId → map_version → map. In the schema, game_stats has separate indexes on mapId and on startTime (twice: startTime and game_stats_startTime_index), but none on both together. No later migration adds one.

So for "the newest 12 games on this map" the database has to either collect all games of the map's versions through mapId and sort them (1.35 million rows for Seton), or walk all games backwards through startTime and check the map of each. Both fit the timings above: fast without the sort, slower the more the map is played.

主要语言
Java
星标
31
派生
30
30 天内没有已合并 PR

环境准备

  • 提供 Dockerfile 或 Docker Compose 文件
  • 没有 Pull Request 模板
  • 没有贡献指南

从这里开始

  1. 先读完整个 Issue，再读项目的贡献指南。
  2. 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
  3. Fork 仓库，在一个分支上完成修改。
  4. 提交 Pull Request，并在描述里引用这个 Issue 编号。

FAForever/faf-java-api 的其他 Issue

查看 FAForever/faf-java-api 的全部 Issue

相似的 Issue

更多 Java Issue

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。