mastodon-japan.netでの投稿をバックアップして、id、published_jst、actor、content_text、inReplyTo、attachmentsの項目を抽出してcsv形式にして、「DB Browser for SQLite」というソフトで検索できるようにしておいたのだが、inReplyToに記載されたURLから、どのidへの返信かは分かるのだけど、各投稿にどのような返信をしたかは分かりにくかった。そこで、"Reply"という項目を追加することにした。SQLiteのコードはよく分からないので、Geminiに相談して実行した。
データベースは、テーブルを変更するのではなく、既存のテーブルからVIEWを作成することにした。この方法だと、VIEWの表示が遅くなる可能性があるので、まずは、inReplyTo カラムにインデックスを貼ることにした。意味が分からなかったのでGeminiに尋ねたら次のような回答。
「インデックスを貼る」という言葉は、データベースを使い慣れていないと少し分かりにくいですよね。
結論から言うと、「テーブルそのものを作る」ことと「そのテーブルの中にインデックス（索引）を作る」ことは別物です。
論理的に整理して解説します。
1. インデックスとは「本の索引」のようなもの
5万件のデータがあるテーブルを「分厚い本」だと想像してください。
- インデックスがない状態:特定の inReplyTo（返信先）を探すとき、SQLiteは本を1ページ目から最後まで全部めくって探さなければなりません（フルスキャン）。5万行分これを繰り返すと、非常に時間がかかります。
- インデックスがある状態:本の巻末にある「索引ページ」を作るようなものです。「どのIDが、どのページにあるか」が整理されたリストが別途作成されます。
「テーブルのインデックス」と「カラムのインデックス」の違い
「テーブルのインデックスを作成する」と言った場合、通常は**「そのテーブルの中の、特定の項目（カラム）に対して索引を作る」**ことを指します。
今回のケースで言うと：
- 対象テーブル:
mastodon-japan- 対象カラム:
inReplyToに対してインデックスを作ります。これを行うことで、
WHERE sub.inReplyTo = ...という計算が数万倍速くなります。
2. なぜ inReplyTo に貼る必要があるのか？
今回の処理（Replyリストの作成）では、以下の動きをします。
「ID：あいうえお」に対して、「誰が自分（あいうえお）を inReplyTo に指定しているか？」を全データの中から探す。
この「探す」という動作が
inReplyToカラムに対して行われるため、ここに索引（インデックス）がないと、5万件 × 5万件に近い膨大な計算が発生してフリーズしたようになります。
3. 具体的にどうすればいいか
以下のコマンドを1回実行するだけで完了します。これはテーブルの設定情報を書き換えるだけで、元の投稿データが消えたり変わったりすることはありません。
SQL
CREATE INDEX idx_in_reply_to ON "mastodon-japan"("inReplyTo");
idx_in_reply_to: インデックスに付ける名前です（自由に変えられます）。"mastodon-japan": 対象のテーブル名。("inReplyTo"): 索引を作りたい項目名。実行後の変化
このコマンドを実行した後に、先ほどの「ビューの作成」や「UPDATE文」を実行すると、処理速度が劇的に（体感できるレベルで）速くなります。
まとめ：論理的なステップ
- インデックス作成:
inReplyToへの道しるべを作る（一瞬で終わります）。- ビュー作成: その道しるべを使って、高速に「Reply」を表示させる。
この「インデックス作成」の操作、実行してみてもよろしいでしょうか？（もしSQLiteのGUIツールなどをお使いであれば、そのツール上の「インデックス」という項目からも作成可能です）
これで、inReplyToの索引ができた。
次に、次のSQLを実行した。
CREATE VIEW "mastodon_japan_view" AS
SELECT
"id",
"published_jst",
(
SELECT group_concat(sub.id, ', ')
FROM "mastodon-japan" AS sub
WHERE sub.inReplyTo = "mastodon-japan".id
) AS "Reply", -- 3番目に配置
"content_text",
"inReplyTo",
"attachments",
"actor" -- 最後に配置
FROM "mastodon-japan";VIEWの作成に失敗した場合は、次のコードでいったん削除する。
DROP VIEW IF EXISTS "mastodon_japan_view";これで、自分の投稿の流れ（スレッド）を追えるようになった。
コメント