如何优化这个十几万数据执行8分钟的sql查询语句

Question

ON后面的连接字段与WHERE后面的条件字段都加上索引。

Answer 1

TRY：select
    ticket0_.id as col_0_0_, 
    clientacco4_.name as col_1_0_, 
    topic6_.name as col_2_0_, 
    subject5_.name as col_3_0_, 
    channel8_.site_type as col_4_0_, 
    ticket0_.datetime_posted as col_5_0_, 
    ticket0_.is_article as col_6_0_, 
    case when ticket0_.is_article=1 then article1_.title else comment2_.title end as col_7_0_, 
    case when ticket0_.is_article=1 then article1_.content else comment2_.content end as col_8_0_, 
    ticket0_.is_relevant as col_9_0_,
    ticket0_.normalised_priority_score as col_10_0_, 
    ticket0_.normalised_sentiment_score as col_11_0_, 
    ticket0_.datetime_status as col_12_0_, 
    ticketstat9_.description as col_13_0_, 
    staffusera3_.user_id as col_14_0_, 
    ticket0_.lock_by as col_15_0_,
    concat(ticket0_.confidence_score, '%') as col_16_0_ 
from
    (
SELECT * FROM Ticket WHERE status=0 
and datetime_status>='2009-03-01 00:00:00' 
and datetime_status<'2009-03-10 23:59:00'
) ticket0_
    left outer join Article article1_ on ticket0_.article_id=article1_.id 
    left outer join Comment comment2_ on ticket0_.comment_id=comment2_.id 
    left outer join Staff_User_Account staffusera3_ on ticket0_.staff_user_account_id=staffusera3_.id, 
    Client_Account clientacco4_, 
    Subject subject5_, 
    Topic topic6_, 
    Channel channel8_, 
    Ticket_Status ticketstat9_ 
where
    ticket0_.client_account_id=clientacco4_.id 
    and ticket0_.subject_id=subject5_.id 
    and subject5_.topic_id=topic6_.id 
    and ticket0_.channel_id=channel8_.id 
    and ticket0_.status=ticketstat9_.id     
order by
    ticket0_.datetime_status DESC

Answer 2

执行explain 后结果如下
select_type  table          type        possible_keys                key                           rows 
SIMPLE       ticketstat9_   const       PRIMARY                      PRIMARY                       1
SIMPLE       clientacco4_   All         PRIMARY                      NULL                          98
SIMPLE       ticket0_       ref         uk_....相关涉及                 fk_ticket_client_account      8016
SIMPLE       article1_      eq_ref      PRIMARY                      PRIMARY                       1
SIMPLE       comment2_      eq_ref      PRIMARY                      PRIMARY                       1
SIMPLE       staffusera3_   eq_ref      PRIMARY                      PRIMARY                       1
SIMPLE       subject5_      eq_ref      PRIMARY,fk_Subject_Topics    PRIMARY                       1
SIMPLE       topic6_        eq_ref      PRIMARY                      PRIMARY                       1
SIMPLE       channel8_      eq_ref      PRIMARY                      PRIMARY                       1