mysql> desc user;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| username   | varchar(255) | NO   | UNI | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

mysql> desc topic;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| group_id   | int(11)      | YES  | MUL | NULL    |                |
| user_id    | int(11)      | YES  | MUL | NULL    |                |
| touched_at | datetime     | NO   |     | NULL    |                |
| deleted_at | datetime     | YES  | MUL | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

索引：

KEY `IDX_9D40DE1BFE54D947` (`group_id`),
KEY `IDX_9D40DE1BA76ED395` (`user_id`),
KEY `IDX_9D40DE1BFE54D9474AF38FD18C43597D` (`group_id`,`deleted_at`,`touched_at`),
KEY `IDX_9D40DE1B4AF38FD18C43597D` (`deleted_at`,`touched_at`),
KEY `IDX_9D40DE1BA76ED3954AF38FD18C43597D` (`user_id`,`deleted_at`,`touched_at`),

查询：

explain select title from topic join user on (user.id = topic.user_id) where deleted_at is null order by touched_at desc;

explain select title from topic join user on (user.id = topic.user_id) where group_id = 1 and deleted_at is null order by touched_at desc;

explain select title from topic join user on (user.id = topic.user_id) where user_id = 1 and deleted_at is null order by touched_at desc;

explain select title from topic join user on (user.id = topic.user_id) where topic.id = 1 and deleted_at is null order by touched_at desc;

这些查询都用到了索引，没有使用 filesort

但是如果没有第 3 个和第 5 个索引，则第 2 条和第 3 条语句都使用 filesort

我的问题是，真的需要第 3 个和第 5 个索引吗？不能通过第 4 个索引不能满足？有没有其他办法？