inner join 下 order by 排序不走索引

sql 如下:

select i.* from (select * from illusts order by artist_id,type,create_date)  i  join (select artist_id from user_artist_followed where user_id=53)u using(artist_id) where type='illust' order by create_date desc ;

illust 表下建了个索引(artist_id,type,create_date),user_artist_followed 表建了索引(user_id,artist_id)

查询计划显示似乎先进行了 filesort 之后才走了索引

想请问一下,是否有优化方法,消除 filesort 和临时表

create table

-- auto-generated definition
create table illusts
(
    illust_id       bigint auto_increment comment '主键 id'
        primary key,
    title           varchar(1023)                         not null comment '标题',
    type            varchar(20)                           not null comment '1:ugoira、2:manga、3：illust',
    caption         text      default ''                  not null comment '附言',
    `restrict`      tinyint                               not null comment '限制',
    artist          text                                  not null comment '画师 Json',
    tools           varchar(255)                          not null comment '作画作画工具',
    tags            text                                  null comment '标签 json',
    create_date     datetime                              not null comment '创建时间',
    page_count      int       default 0                   not null comment '页数',
    width           int                                   not null comment '宽度',
    height          int                                   not null comment '高度',
    sanity_level    tinyint                               not null comment '情色级别',
    x_restrict      tinyint                               not null comment '十八禁限制',
    total_bookmarks int                                   not null comment '收藏数',
    total_view      int                                   not null comment '查看数',
    image_urls      longtext  default ''                  not null comment '图片链接',
    artist_id       int                                   not null comment '画师 id',
    update_time     timestamp default current_timestamp() not null on update current_timestamp() comment '更新时间'
)
    comment '插画表' charset = utf8mb4;

create index artist_id_type_create_date_index
    on illusts (artist_id, type, create_date);

create index update_time_total_bookmarks_index
    on illusts (update_time, total_bookmarks);

-- auto-generated definition
create table user_artist_followed
(
    id          int auto_increment
        primary key,
    user_id     int      null comment '用户 id',
    artist_id   bigint   null comment '画师 id',
    create_date datetime null
);

create index user_id_artist_id_create_date
    on user_artist_followed (user_id, artist_id, create_date);