降順(DESC)の インデックス だけどfilesortを出したくないとき
上記の SQL 、実際に数万件以上のデータで試してみればわかりますが、EXPLAINで分析すると、using filesortがでます。
これは実は簡単な話で、実際には インデックス にDESCを指定しても、無視して ASC で インデックス を作成しているからです。本来であれば、並べ替えにも インデックス が使われるはずが、 インデックス の並び順が ASC なので、各 レコード のデータを読み込んで ソート します。この時に、一時ファイルを使って ソート を行っているのですが、これがusing filesortの正体です。
つまり、using filesortが出ているということは ソート に インデックス が使われていないことを意味します。
しかし、 SNS における日記や伝言板、ミニメールなど、扱うデータが膨大で参照される頻度も高い場合には（できるかぎりテーブルの分割なども行うのですが）filesortはできれば避けたいところです。
そういう場合には、以下のようにします。（以下は一般的な日記を想定してみました）
ようするに、本来であれば公開日時であるopen_dtで ソート を行うのですが、 SQL をより高速化するために、ソート 用のカラムをひとつ作成します。
そして、open_dtが更新される際には、必ずorderも一緒に更新されるように作ります。
上記のような SQL でorderカラムを更新するようにすれば、常にopen_dtとはまったく逆の順序になりますので、「ORDER BY open_dt DESC」とやっている個所を「ORDER BY `order` ASC 」とすればよいだけです。
そして、`order`は ASC で指定されておりますので、これを複合 インデックス に含めれば、 ソート にも複合 インデックス が使われてfilesortが発生しなくなります。
とりあえず上記が基本となります。
idを大きい順に取得したいときなど、ORDER BY id DESCとしますが、
対象が大きい場合にはUsing filesortとなってしまいます。
こちらの記事ではそれを起こさない方法が照会されています。
