Claude の多段レビュー（7観点の並列レビュー＋反証検証）で浮かんだ「外部キーの列にインデックスが無かった」話を、勉強のためにまとめたものです。
綴Photo のモバイル版は、写真と「綴り（気持ち）」をローカルの SQLite に保存していて、綴りテーブルの写真ID列は写真テーブルを指す外部キーになっている。レビューで分かったのは、その外部キーの列にインデックスが張られていなかったこと。やや直感に反するこの落とし穴をまとめておく。
コードで起きていたこと
SQLite は、主キーや UNIQUE 制約を付けた列には自動でインデックスを張ってくれます。ところが外部キーの列には自動でインデックスを張りません。綴Photo では、綴りテーブルの写真ID列がまさにこの「外部キーだがインデックスが無い列」だった。
写真の詳細画面は、この写真ID列で綴りを絞り込む（「この写真に紐づく綴りを全部出す」）クエリを watch していた。本棚の一覧画面も、写真テーブルと綴りテーブルを写真IDで突き合わせて1つの表にする（join）操作で表示していた。
索引が無いと、SQLite はその場限りの一時インデックスを内部で構築することもあります。綴りの件数が少ないうちは問題にならないが、増えるほど効いてきます。しかも watch なので、データが変わるたびにこの走査が繰り返されます。1回読んで終わる SELECT なら「重くなったら気づく」で済むかもしれないが、watch は画面が開いている間ずっと張り続けるため、機会は何倍にも膨らみます。
なぜそうなりやすいか
主キーと UNIQUE に自動でインデックスが付くので、「制約を張った列には索引も付く」という感覚が育ちやすいです。外部キーも「制約」の一種なので、同じように索引も付いていると錯覚しがちです。これは SQLite に限らず多くのデータベースで共通の挙動だが、知らないと「結合に使っている列なのに遅い」原因として見落とします。
「親（写真）を起点に子（綴り）を引く」「親と子を結合する」というのは、外部キーがある関係ならほぼ必ずやる操作です。その操作で使う列にインデックスが無いと、関係の主要な使い道がそのまま遅い経路になります。外部キーを張ったら、その列で結合・絞り込みをするかどうかを考え、するならインデックスもセットで張ります。この2ステップはセットだが、「制約を書いた = 索引も付く」という先入観があると抜けやすいです。
改善の方向
綴りテーブルの写真ID列にインデックスを明示的に追加した。Drift ではテーブル定義の上に @TableIndex を書くと、新規インストール時のスキーマ作成にインデックスが含まれます。
@TableIndex(name: 'idx_meanings_photo_id', columns: {#photoId})
class Meanings extends Table {
// ...
}新規インストールはこれで索引付きのテーブルが作られます。ただし、既にアプリを使っている人のデータベースには @TableIndex を書いただけでは索引が追加されません（テーブルは既に作られているので）。そこでスキーマのバージョンを一つ上げて、既存のデータベースにも索引を足すマイグレーションを書いた。
// 既存DB向け: 索引が無ければ追加する
await customStatement(
'CREATE INDEX IF NOT EXISTS idx_meanings_photo_id ON meanings(photo_id)',
);IF NOT EXISTS を付けているのは、新規インストール側では @TableIndex 経由で既に同名の索引が張られているので、二重作成でエラーにしないためです。地味だが大事な辻褄合わせです。
学びポイント
- 外部キー列に索引が自動で付かないのは、SQLite を含む多くのDBで共通
- 外部キーを張ったら「その列で結合・絞り込みするか?」を考え、するならインデックスもセットで張る
@TableIndex（スキーマ定義）だけでは既存ユーザーのDBは更新されない。バージョンを上げてマイグレーションでCREATE INDEX IF NOT EXISTSを足す
この同じ綴りテーブルの外部キーには、もう一つ別の落とし穴があった。「宣言した外部キーが PRAGMA foreign_keys = ON 無しでは強制されていなかった」という話で、別記事「宣言しただけでは守られない」にまとめた。どちらも「スキーマに書いた／宣言しただけでは、思った挙動になっていない」という根は同じです。
この記事は2026年6月時点の自分の理解で書いています。仕様や認識が変わってる可能性があります。