我已經寫了一個查詢。它效果更好。但目前，所有表都有 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
請注意此處「病人 ID」是 varchar。
這可能運行得更快：
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這些索引可能有幫助：
由於
OR優化不佳，可能最好為communicate_with_pt選擇 NULL 或空字串（以避免對兩者進行測試）。