我已经写了一个查询。它效果更好。但目前，所有表都有 100K 行，并且我的一个查询返回速度太慢。您能否建议我如何优化查询？
1
704select *
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。
Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
收藏
这可能运行得更快：
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 或空字符串（以避免对两者进行测试）。