MySQL ORDER BY + LIMIT のオプティマイザ誤選択を修正してAPIレスポンスを5分→20秒に改善した話

記事のサマリー（TL;DR）

• freee請求書の一覧取得APIで最大5分かかっていたスロークエリを、MySQL optimizer trace で調査し20秒まで短縮
• ORDER BY parents.id ASC LIMIT 100 の存在がトリガーとなり、オプティマイザがcompany_id系インデックスからPRIMARY（フルスキャン）へ切り替えていた
• JOINするchildrenテーブルの検索条件に company_id を1行追加するだけで、実際の走査行数が約4,000万行→約10万行（1/400）に激減

kintone・Salesforce・freee などのSaaS専用UI開発でも踏み抜きやすいパターン

freeeのような多テナント型SaaSでは「事業所ID（company_id）でテナントを分離しながら、日付・ステータスで絞り込む」構造が非常に一般的です。kintoneやSalesforceの専用UI補完をRailsで実装する場合も、アプリケーション側のスコープ設計によってはJOIN先のWHERE条件が意図せず抜け落ち、同様のオプティマイザ誤選択が起きるリスクがあります。

さらに、MCP（Model Context Protocol）経由でAIエージェントがAPIを自動的に叩くユースケースが広がっている昨今、一覧取得系のエンドポイントは人間のオペレーションよりも高頻度・連続的に呼び出される可能性があります。GA4や広告データと組み合わせた経営分析基盤を構築する際も、集計用クエリが同様の罠を踏む可能性があるため、EXPLAIN ANALYZE + optimizer trace の確認は定期的に行う価値があります。

詳細

起きていたこと

• 対象: freee請求書の一覧取得APIエンドポイント
• 事象: データ量の多い事業所からのリクエストでレスポンスに約5分かかっていた
• 結果: 原因を修正し、同じ条件でのレスポンスタイムを約20秒まで短縮

対象のテーブル構成

対象データは3つのテーブルに分かれて格納されています。「親-バージョン管理用の中間-種別固有の子」型のよくある構造です。

[コードは原文をご参照ください]

日付での絞り込み条件が指定されると、この3テーブルをJOINしたクエリが発行されます。

問題のクエリ

本番相当のデータが入った検証環境で EXPLAIN ANALYZE を実行しました。

[コードは原文をご参照ください]

実行結果の要点は以下の通りです。

• 見積もりでは PRIMARY インデックスを使って約10万行を読む予定だったが、実際には約4,000万行を読み込んでいた
• actual time がちょうど180秒・rows=0・warning 1件で終了 → 完走ではなく実行時間上限による打ち切り
• parents.company_id = X という条件があるのに、company_id 系インデックスが使われていない

関連するインデックス

EXPLAINの結果では、parents テーブルが type=index（PKのフルインデックススキャン）になっており、company_id を含むインデックスは候補に存在するにもかかわらず選ばれていませんでした。

原因: MySQLオプティマイザが選択を誤っていた

MySQLには optimizer trace という、オプティマイザの判断過程をJSONで出力する機能があります。これで問題のクエリを確認したところ、以下の2段階の判断が起きていました。

1. considered_execution_plans フェーズ: 結合順序とアクセス方法のコスト比較で index_parents_on_company_and_content（セカンダリインデックス）が採用された
2. reconsidering_access_paths_for_index_ordering フェーズ: ORDER BY のためにPRIMARYへの切り替えが起きた

オプティマイザの判断は次のように推測されます。

1. クエリ末尾の ORDER BY parents.id ASC LIMIT 100 に着目する
2. 「PRIMARY KEYを順に辿ればソート処理を省略できる」と考える
3. company_id 系インデックスで絞り込んでから並べ替えるより、PKスキャンでソートを省略した方が総コストが低いと判断する
4. 結果、parents テーブル全体をPK順にフルスキャンする実行計画が選ばれる

この誤判断の遠因は、children 側のWHERE条件にありました。現状のクエリでは children に対する条件が content_id による絞り込みしかなく、「1行引くだけの軽い操作」と見積もられていました。そのためJOIN全体のコストが低く見え、parents 側でPKスキャン（ソート省略）を選んでも総コストが安いと判断されていました。

一方、children 側にも company_id の条件が加わると、オプティマイザは「条件に合う行数」の見積もりにその選択率を掛け合わせるため、見積もりが大きく縮みます。「PKを少したどればLIMIT 100件がすぐ見つかる」という前提が崩れ、PKスキャンへの切り替えが起きなくなります。

つまり **「children 側に company_id の条件がなかったため、オプティマイザが『JOIN先は軽い → parents側はPKスキャンでも大丈夫』と誤判断していた」**というのが根本原因です。

修正内容

修正候補として以下を検討しました。

変更箇所（Railsの擬似コード）

[コードは原文をご参照ください]

発行されるSQLの children 側に AND children.company_id = X が加わるだけの差分です。company_id はキーワード引数（デフォルト nil）として追加したため、この日付フィルタを利用している他の呼び出し元は引数を指定しなければ従来と同じ振る舞いのままです。

修正後の効果

parents 側の走査行数は約1/400に減少。本番リリース後のモニタリングでは、対象エンドポイントのプロセスタイムが最大5分から20秒程度に改善されました（クエリ単体ではなくAPI処理全体の時間）。

補足: 別のアプローチでも同じ結論に至った

「オプティマイザが ORDER BY の存在によって判断を変えている」という仮説を検証するため、2つの追加実験を行いました。

① ORDER BY を外してEXPLAIN

ORDER BY parents.id を消してから同じクエリを実行すると、company_id 系インデックスが正しく選ばれ、実行時間も約1秒に収まりました。仮説と辻褄が合います。

② prefer_ordering_index をoffにしてEXPLAIN

MySQLには「並び順のためにインデックスを優先する」挙動を制御する prefer_ordering_index フラグがあります。MySQL公式ドキュメントには次のように記載されています。

Because the algorithm that makes this determination cannot handle every conceivable case (due in part to the assumption that the distribution of data is always more or less uniform), there are cases in which this optimization may not be desirable. This optimization can be disabled by setting the prefer_ordering_index flag to off.

（このアルゴリズムはすべてのケースに対応できるわけではなく、この最適化が望ましくないケースがある。そのような場合は prefer_ordering_index フラグをoffにすることで無効化できる。）

[コードは原文をご参照ください]

を入れてから同じクエリを実行すると、こちらも正しいインデックスに切り替わりました。

参考リンク:

• MySQL Reference Manual: Switchable Optimizations
• MySQL Worklog WL#13929
• MySQL Bugs #97001

まとめ

• MySQL オプティマイザは ORDER BY ... LIMIT があるとき、「並び順を持つインデックス（PRIMARYなど）を辿ればソートを省略できる」と判断して意外な実行計画を選ぶことがある
• JOINのコスト見積もりは、両テーブルのWHERE条件とインデックスの状況の両方に影響される
• 今回は「片側のテーブルに絞り込み条件を足す」だけで、もう片方のテーブルのインデックス選択が修正された
• 実行計画に違和感を覚えたら、optimizer trace と prefer_ordering_index を確認する
• MCPを経由してAIエージェントが一覧取得系APIを高頻度・連続的に呼び出すユースケースが広がる中、APIパフォーマンス改善の重要性は今後さらに高まる