안녕하세요. 최근 몇몇 쿼리들이 느려서 CPU 가 많이 먹는 현상때문에 쿼리 튜닝을 진행하고 있습니다. 

질문하고자하는 테이블 정의, 쿼리 및 실행계획은 아래와 같습니다. 

기존 1.5초 정도에서 orderby 를 제거하면 0.3초까지 줄어드는 상황입니다. 

데이터가 많지 않은데 왜이렇게 느리게 쿼리가 실행된느지 모르겠습니다....

의견 부탁드립니다.! 

감사합니다 :)

-- auto-generated definition
create table bots
(
    idx                  int auto_increment
        primary key,
    fk_user              int                            not null,
    uuid                 char(16)                       not null,
    name                 varchar(200)                   not null comment '챗봇 이름',
    description          text                           null comment '봇 설명',
    greeting             varchar(1000) default '안녕?'    not null comment '첫 인사',
    bot_type             varchar(20)   default 'COMMON' not null comment '봇 타입',
    chatting_type        char                           null comment 'D: Default(기본채팅), C: CUSTOM(맞춤채팅)',
    thumbnail_path       varchar(255)                   null comment '봇 썸네일 이미지 경로',
    background_path      varchar(255)                   null comment '봇 프로필 배경 이미지 경로',
    is_formal            tinyint(1)    default 0        not null comment '존댓말 여부 (삭제 예정)',
    is_curse             tinyint       default 1        not null comment '욕설 여부',
    has_advanced_persona tinyint(1)    default 0        not null comment '고급 페르소나 초기화 여부',
    is_public            tinyint(1)    default 1        not null comment '공개 여부',
    is_info_public       tinyint(1)    default 0        not null comment '페르소나, 가르치기 정보 공개 여부',
    rate                 float         default 0.5      not null comment '공통봇 비율',
    favorite_at          datetime                       null comment '즐겨찾기 한 시간',
    first_public_at      timestamp                      null comment '봇이 최초로 공개된 시간',
    forced_non_public_at timestamp                      null comment '관리자에 의해 비공개로 전환된 시간',
    frozen_at            timestamp                      null,
    deleted_at           timestamp                      null,
    created_at           datetime                       null,
    updated_at           datetime                       null,
    creation_step        varchar(20)                    not null,
    constraint bots_idx_uindex
        unique (idx),
    constraint bots_uuid_uindex
        unique (uuid)
);



create index idx_bots_deleted_at
    on bots (deleted_at);

create index idx_bots_fk_user_filter
    on bots (fk_user, creation_step, bot_type, deleted_at);

explain analyze select b1_0.idx
from bots b1_0
         left join bot_stats b2_0 on b1_0.idx = b2_0.fk_bot
where b1_0.deleted_at is null
  and b1_0.is_public = 'true'
  and b1_0.creation_step = 'COMPLETED'
  and b1_0.thumbnail_path is not null
order by b2_0.chat_count desc, b2_0.like_count desc, b1_0.first_public_at desc
limit 13;

-- auto-generated definition
create table bot_stats
(
    idx          int auto_increment
        primary key,
    fk_bot       int                                not null,
    chat_count   int      default 0                 not null,
    like_count   int      default 0                 not null,
    report_count int      default 0                 not null,
    created_at   datetime default CURRENT_TIMESTAMP not null,
    updated_at   datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP,
    constraint uk_fk_bot
        unique (fk_bot),
    constraint fk_bot_stats_bot
        foreign key (fk_bot) references bots (idx)
            on delete cascade
);

create index idx_bot_stats_fk_bot
    on bot_stats (fk_bot);

-> Limit: 13 row(s)  (actual time=1434.637..1434.639 rows=13 loops=1)
    -> Sort: b2_0.chat_count DESC, b2_0.like_count DESC, b1_0.first_public_at DESC, limit input to 13 row(s) per chunk  (actual time=1434.636..1434.637 rows=13 loops=1)
        -> Stream results  (cost=4795.70 rows=848) (actual time=0.166..1409.404 rows=115866 loops=1)
            -> Nested loop left join  (cost=4795.70 rows=848) (actual time=0.164..1384.137 rows=115866 loops=1)
                -> Filter: ((b1_0.is_public = 0) and (b1_0.creation_step = 'COMPLETED') and (b1_0.thumbnail_path is not null))  (cost=4276.67 rows=848) (actual time=0.141..702.689 rows=115866 loops=1)
                    -> Index lookup on b1_0 using idx_bots_deleted_at (deleted_at=NULL), with index condition: (b1_0.deleted_at is null)  (cost=4276.67 rows=94184) (actual time=0.133..652.613 rows=148409 loops=1)
                -> Single-row index lookup on b2_0 using uk_fk_bot (fk_bot=b1_0.idx)  (cost=0.51 rows=1) (actual time=0.006..0.006 rows=1 loops=115866)