我编写了一个由多个连接和sum（if（））条件组成的查询。我正在使用mysql数据库。查询结构看起来是这样的：*来自多个表的查询连接（准确地说是7个）和sum（if（））条件表示我们总共53个状态。*执行查询几乎需要20分钟，这是一个非常长的周期。
如果有人能建议我如何减少这么多的时间。

SELECT ag.position_id AS 'position_id',
    ag.npn AS 'NPN',
    concat(ag.first_name, ' ', ag.last_name) AS 'full_name',
    lb.name AS 'lob', 
    location_lic AS 'loc', 
    COUNT(DISTINCT lt.id) AS 'RTS_Total',
    SUM(IF(st.id = 1, 1, 0)) AS 'AK', 
    SUM(IF(st.id = 2, 1, 0)) AS 'AL', 
    SUM(IF(st.id = 3, 1, 0)) AS 'AR', 
    SUM(IF(st.id = 4, 1, 0)) AS 'AZ', 
    SUM(IF(st.id = 5, 1, 0)) AS 'CA', 
    SUM(IF(st.id = 6, 1, 0)) AS 'CO', 
    SUM(IF(st.id = 7, 1, 0)) AS 'CT', 
    SUM(IF(st.id = 8, 1, 0)) AS 'DC', 
    SUM(IF(st.id = 9, 1, 0)) AS 'DE', 
    SUM(IF(st.id = 10, 1, 0)) AS 'FL', 
    SUM(IF(st.id = 11, 1, 0)) AS 'GA', 
    SUM(IF(st.id = 12, 1, 0)) AS 'HI', 
    SUM(IF(st.id = 13, 1, 0)) AS 'IA', 
    SUM(IF(st.id = 14, 1, 0)) AS 'ID', 
    SUM(IF(st.id = 15, 1, 0)) AS 'IL', 
    SUM(IF(st.id = 16, 1, 0)) AS 'IN', 
    SUM(IF(st.id = 17, 1, 0)) AS 'KS', 
    SUM(IF(st.id = 18, 1, 0)) AS 'KY', 
    SUM(IF(st.id = 19, 1, 0)) AS 'LA', 
    SUM(IF(st.id = 20, 1, 0)) AS 'MA', 
    SUM(IF(st.id = 21, 1, 0)) AS 'MD', 
    SUM(IF(st.id = 22, 1, 0)) AS 'ME', 
    SUM(IF(st.id = 23, 1, 0)) AS 'MI', 
    SUM(IF(st.id = 24, 1, 0)) AS 'MN', 
    SUM(IF(st.id = 25, 1, 0)) AS 'MO', 
    SUM(IF(st.id = 26, 1, 0)) AS 'MS', 
    SUM(IF(st.id = 27, 1, 0)) AS 'MT', 
    SUM(IF(st.id = 28, 1, 0)) AS 'NC', 
    SUM(IF(st.id = 29, 1, 0)) AS 'ND', 
    SUM(IF(st.id = 30, 1, 0)) AS 'NE', 
    SUM(IF(st.id = 31, 1, 0)) AS 'NH', 
    SUM(IF(st.id = 32, 1, 0)) AS 'NJ', 
    SUM(IF(st.id = 33, 1, 0)) AS 'NM', 
    SUM(IF(st.id = 34, 1, 0)) AS 'NV', 
    SUM(IF(st.id = 35, 1, 0)) AS 'NY', 
    SUM(IF(st.id = 36, 1, 0)) AS 'OH', 
    SUM(IF(st.id = 37, 1, 0)) AS 'OK', 
    SUM(IF(st.id = 38, 1, 0)) AS 'OR', 
    SUM(IF(st.id = 39, 1, 0)) AS 'PA', 
    SUM(IF(st.id = 40, 1, 0)) AS 'PR', 
    SUM(IF(st.id = 41, 1, 0)) AS 'RI', 
    SUM(IF(st.id = 42, 1, 0)) AS 'SC', 
    SUM(IF(st.id = 43, 1, 0)) AS 'SD', 
    SUM(IF(st.id = 44, 1, 0)) AS 'TN',
     SUM(IF(st.id = 45, 1, 0)) AS 'TX', 
    SUM(IF(st.id = 46, 1, 0)) AS 'UT', 
    SUM(IF(st.id = 47, 1, 0)) AS 'VA', 
    SUM(IF(st.id = 48, 1, 0)) AS 'VI', 
    SUM(IF(st.id = 49, 1, 0)) AS 'VT', 
    SUM(IF(st.id = 50, 1, 0)) AS 'WA', 
    SUM(IF(st.id = 51, 1, 0)) AS 'WI', 
    SUM(IF(st.id = 52, 1, 0)) AS 'WV', 
    SUM(IF(st.id = 53, 1, 0)) AS 'WY' 
FROM cxprtsapp_licensetracker lt 
INNER JOIN cxprtsapp_agents ag ON ag.id = lt.agent_id_id AND ag.position_status LIKE 'Active' 
LEFT JOIN cxprtsapp_statelobjit slj ON slj.state_id = lt.state_id_id AND ag.lob_id = slj.lob_id
LEFT JOIN 
   (select npn, rtsreport_appointed, rtsreport_licensed, state_id, lob_id 
    from cxprtsapp_rtslob 
    order BY refresh_number desc
   ) rts ON rts.state_id = lt.state_id_id and ag.lob_id = rts.lob_id and ag.npn=rts.npn
INNER JOIN cxprtsapp_location lc ON lc.id = ag.loc_id 
INNER JOIN cxprtsapp_lineofbussiness lb ON lb.id = ag.lob_id 
INNER JOIN cxprtsapp_states st ON st.id = lt.state_id_id 
LEFT JOIN 
   (SELECT DISTINCT npn, state_code, status 
    FROM cxprtsapp_pdbappointments 
    WHERE status LIKE 'Appointed'
   ) appt ON appt.npn = ag.npn AND appt.state_code = st.state_code 
WHERE certification_id IS NOT NULL 
AND (expiration_date IS NULL OR expiration_date > CURDATE()) 
AND (slj.jit = 1 OR rts.rtsreport_appointed = 1 OR appt.status IS NOT NULL) 
GROUP BY ag.id, ag.lob_id,loc_id ORDER BY ag.hire_date DESC;

解决方案：
查询所用的时间已从10-15分钟减少到10-20秒。
我们必须改变数据库索引以提高性能。
我们必须首先在数据库中运行下面的sql查询。
‌

ALTER TABLE cxprtsapp_agents ADD INDEX cxprtsapp_agents_idx_status_id_id_npn_id (position_status,id,lob_id,npn,loc_id);
ALTER TABLE cxprtsapp_licensetracker ADD INDEX cxprtsapp_licensetra_idx_id_id_id_id (agent_id_id,state_id_id,certification_id,id);
ALTER TABLE cxprtsapp_lineofbussiness ADD INDEX cxprtsapp_lineofbuss_idx_id_name (id,name);
ALTER TABLE cxprtsapp_location ADD INDEX cxprtsapp_location_idx_id_lic (id,location_lic);
ALTER TABLE cxprtsapp_statelobjit ADD INDEX cxprtsapp_statelobji_idx_id_id (state_id,lob_id);
ALTER TABLE cxprtsapp_states ADD INDEX cxprtsapp_states_idx_id_code (id,state_code);
ALTER TABLE cxprtsapp_rtslob ADD INDEX cxprtsapp_rtslob_idx_numbe_npn_appoi_licen_id_id (refresh_number,npn,rtsreport_appointed,rtsreport_licensed,state_id,lob_id);
ALTER TABLE cxprtsapp_pdbappointments ADD INDEX cxprtsapp_pdbappoint_idx_status_npn_code (status,npn,state_code);