File sort occurs even if compound index is used when using in clause and order by

The following table adds a composite index of id, position to .

+--------------------+
| id|position|
+------+----------+
|    1 |        1 |
|    2 |        2 |
|    3 |        5 |
|    4 |        4 |
+------+----------+

show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Columnn_name | Collation | Cardinity | 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 | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

If I issue a query like this below, it looks like a composite index is being used, but filesort will occur. Why does filesort occur?Also, is there a way to avoid filesort?

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

mysql sql

2022-09-30 20:13

1 Answers

The idx of the composite index is sorted by id and then only the overlapping parts are sorted by position.Therefore, order by position can only be sorted.

If idx is reversed to id, position and position, id, sorting is not necessary, but this time where id in (1,3,4) is no longer available to search for where id in (1,3,4), and the end of the book is reversed.Therefore, sorting is a must.

2022-09-30 20:13

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656