I have written a query. It works better. But currently, all tables have 100K rows, and one of my queries is returning too slowly. Can you suggest me how to optimize my query?
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
Please note that the "patient ID" here is varchar.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Thismayrun faster:
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 2000These indexes may be helpful:
Due to poor
ORoptimization, it may be better to choose NULL or the empty string forcommunicate_with_pt(to avoid testing both).