

以下のようなテーブルがに対してid, positionで複合indexを追加します。

[code]

    +------+----------+
    | id   | position |
    +------+----------+
    |    1 |        1 |
    |    2 |        2 |
    |    3 |        5 |
    |    4 |        4 |
    +------+----------+
    
    show index from test;
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | test  |          1 | idx      |            1 | id          | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
    | test  |          1 | idx      |            2 | position    | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    
[/code]

この状態で以下のようなクエリを発行すると複合indexが使われているように見えますがfilesortが発生してしまいます。なぜfilesortが発生してしまうのでしょうか？また、filesortを回避する方法はありますでしょうか？

[code]

    mysql> explain
        -> select
        ->   *
        -> from
        ->   test
        -> where
        ->   id in (1,3,4)
        -> order by position;
    +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
    | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                    |
    +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
    |  1 | SIMPLE      | test  | range | idx           | idx  | 5       | NULL |    3 | Using where; Using index; Using filesort |
    +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
    
[/code]


CC BY-SA 3.0
2017-07-20T00:36:09.093
36511
2017-07-20T01:13:33.997
2017-07-20T00:44:19.873
4236
24499
question
mysql sql
in句とorder by を使用した場合に複合indexが使われていてもfile sortが発生してしまう


複合indexの`idx`は`id`順でソートした上で重複した部分についてのみ`position`順でソートされているのではないでしょうか。ですので`order
by position`を実現するにはソートせざるを得ないです。

`idx`を`id, position`の順を逆転させて`position, id`にすればソートは不要になりますが、今度は`where id in
(1,3,4)`の検索に使えなくなってしまい本末転倒です。ですのでソートは必須と思います。


CC BY-SA 3.0
2017-07-20T01:13:33.997
36514
2017-07-20T01:13:33.997
4236
36511
answer