MySQLで複合インデックスを貼る時、貼り方(貼る順番)を気をつけないとほとんど効果がない件

MySQLをデータベースに使ったSNSのシステムで特定ユーザーの投稿一覧を表示する部分が
速度的に問題がないか調査していたらめちゃくちゃ遅いSQLがあったのでなぜか調べていたらどうやらindexがうまく効いてないようだった。

EXPLAINを使ってSQLのtypeを調べてみたら

EXPLAIN SELECT * FROM posts WHERE user_id = 'taro' AND user_type = 'u' ORDER BY id DESC;

const、eq_ref、ref、range、index、allのうち
indexとなっていてフルインデックススキャンが行われていてかなり遅かった。(右にいくほど遅くなる)
これはおかしいと思い

show index from テーブル名;

を行ってみたところ
どうやら適切な複合インデックスが貼られていなかったため遅かった模様。

システム自体はLaravelで構築していたため
migrationを作成し

    public function up()
    {
        Schema::table('posts', function (Blueprint $table) {
            $table->index(['user_id', 'user_type', 'id']);
        });
    }

としてインデックスを追加したら20秒くらいかかっていたのが
0.1秒で取得できるようになった。

複合インデックスを貼る時は気をつけなければいけないことがあって下記のurlより詳細を見てもらうと分かるが
https://qiita.com/keii1111/items/02861c74bcd57ab1e290
インデックスを貼る順序がすごく重要で
例えば、今回のSQLであればuser_id、user_typeというふうに順番にしぼりこみ最後にORDER BYすることが重要
その順序通りに複合インデックスを作成しないとうまい具合に高速に結果を得ることができない。
たとえば

$table->index(['id', 'user_id', 'user_type']);

こうなるとSQLの評価順序とインデックスの順序が合致しないので効果的なインデックスが働きません。
順序なんてなんでもいいからとにかくインデックスつければいいってことではないので注意が必要。