sql优化-子查询索引

sql

select pi.*
, pp.project_type
, pp.project_name
, pp.project_stage
, pp.project_total_investment
, pp.project_state
,ifnull((
select
CASE
WHEN cast(pp.project_state as UNSIGNED INTEGER)    <=    2 THEN
        sum(if(ie.enroll_amount    >    ie.overall_amount, ie.enroll_amount, ie.overall_amount))
WHEN cast(pp.project_state as UNSIGNED INTEGER)    <=    7
and cast(pp.project_state as UNSIGNED INTEGER)    >   2 THEN
        sum(ie.overall_amount)
ELSE
        sum(if(ie.to_account_amount    >   ie.overall_amount, ie.overall_amount, ie.to_account_amount))
END as real_amount
from t_inv_invest_enroll ie
where ie.project_id = pi.project_id and ie.sys_logic_del = 0
),0) followInvestTotalAmountAll
,ifnull(
(
select
CASE
WHEN cast(pp.project_state as UNSIGNED INTEGER)    <=    2 THEN
        sum(if(ie.enroll_amount    >    ie.overall_amount, ie.enroll_amount, ie.overall_amount))
WHEN cast(pp.project_state as UNSIGNED INTEGER)    <=    7
and cast(pp.project_state as UNSIGNED INTEGER)    >    2 THEN
        sum(ie.overall_amount)
ELSE
        sum(if(ie.to_account_amount     >    ie.overall_amount, ie.overall_amount, ie.to_account_amount))
END as real_amount
from t_inv_invest_enroll ie
where ie.project_id = pi.project_id and ie.sys_logic_del = 0
and ie.investor_type='1'
)
,0) followInvestTotalAmountCustomer
from t_inv_project_invest pi
left join t_pmm_project pp on pi.project_id = pp.id
where pi.sys_logic_del = 0
ORDER BY pi.sys_ctime DESC, pi.sys_etime DESC

EXPLAIN

 

发现是子查询的问题，考虑加上普通索引