IN句のサブクエリにUsing filesortが発生する理由がわかりません

Question

• 下記のクエリのように、SELECT文の WHERE IN句に、外側クエリのFROM句と同じテーブル(city)を指定した場合に、外側クエリに特定カラムでソートをすると、なぜか内側クエリにソートが発生してしまいます。(Using filesort)

外側クエリにしかソートを指定していないのに、内側にUsing filesortがかかっているのはなぜでしょうか？

※そもそも、下記の例だとIN句でサブクエリにする必要はないのですが、検証として理由が気になっています。

再現方法は、以下です。MySQL公式のサンプルデータ world を利用しています。
https://dev.mysql.com/doc/index-other.html

SELECT
  c1.id
FROM
  city c1
WHERE c1.id IN  (
  SELECT
    `c2`.`id`
  FROM
    `city` c2
  WHERE
    `c2`.`CountryCode` = 'ABW'
)
ORDER BY
  c1.ID DESC
LIMIT 5

EXPLAIN結果

Answer 1

ORDER BY c1.ID DESC

だからでしょうね。

最初の行のUsing temporary; Using filesortはSQL全体をソートしている事を意味します。

c1.IDはプライマリーですけど、ascですからソートするしか無いという事なんだと思います。
Using filesort

Comments

[退会済みユーザー]

saziさん ご回答ありがとうございます。

> 最初の行のUsing temporary; Using filesortはSQL全体をソートしている事を意味します。
c1に対してORDER BY c1.ID DESC を指定しているのになぜ、SQL全体をソートしているのでしょうか？理解できずで申し訳ないです。参考URL拝見させてもらったのですが、複数テーブルをJOINする場合のケースについて記載がありました。今回の WHERE IN句 の場合もJOIINと同じように考えていいものなのでしょうか？

> c1.IDはプライマリーですけど、ascですからソートするしか無いという事なんだと思います。
こちらは ASC指定にすればソート不要になるという意味でしょうか？手元で試したところDESCと同じくソートが発生してしまいました。

[sazi]

order byを変更しても関わりませんでしたか。
DESCが理由だと思いましたので、そうなると動作的に非効率ですから私も分かりません。
リンク先にUsing temporary; Using filesorとなるケースやならないケースが記述されてますので該当しないか確認して下さい。
さらに理由についてはリンク先にもありますが、こちら（http://s.petrunia.net/blog/?p=24）で。

[sazi]

>c1に対してORDER BY c1.ID DESC を指定しているのになぜ、SQL全体をソートしているのでしょうか？
細かい点については、オプチマイザが効率良いと判断するからとしか言えません。
最終的にソートするなら、ソートしてから行う方が効率のいい場面がある事は想像できますけど。

[sazi]

あくまで推測ですが、サブクエリーで同じテーブルを使用している事から、条件によって間引かれるので、ソートしなおすのかもしれません。
なので、サブクエリーを別なテーブルでの条件にすると変わってくるかもしれません。
この推測はサブクエリー内のWHERE条件の指定をしない事で確認できると思います。

[退会済みユーザー]

いろいろと教えていただきありがとうございました。
有益なキーワードを教えていただいたおかげで自分でもう少し調べてみることができそうです。
ありがとうございました。