Mysql查询返回太慢

Question

我写了一个查询。效果更好。但是目前，所有表都有100K行，而且我的一个查询返回太慢。你能建议我如何优化查询吗?

select * 
from tbl_xray_information X 
WHERE locationCode = (SELECT t.id 
from tbl_location t 
where CODE = '202') 
AND ( communicate_with_pt is NULL || communicate_with_pt='')
AND x.patientID NOT IN (SELECT patientID 
FROM tbl_gxp_information 
WHERE center_id = '202')
order by insertedON desc LIMIT 2000

请注意此处'patientID'是varchar。

Answer 1

这个可能跑得更快:

select  *
from  tbl_xray_information AS X
WHERE  locationCode = 
( SELECT  t.id
from  tbl_location t
where  CODE = '202'
)
AND  ( x.communicate_with_pt is NULL 
OR x.communicate_with_pt = '' )
AND  NOT EXISTS ( SELECT 1 FROM tbl_gxp_information
WHERE x.patientID = patientID
AND center_id = '202' )
order by  insertedON desc
LIMIT  2000 

这些索引可能有帮助:

tbl_location:  INDEX(CODE)
tbl_gxp_information:  INDEX(center_id, patientID)  -- (either order)

由于OR优化得很差，它可能最好为communicate_with_pt选择NULL或空字符串(以避免同时测试)。