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.
P粉1248907782024-03-31 00:07:43
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 2000
These indexes may be helpful:
tbl_location: INDEX(CODE) tbl_gxp_information: INDEX(center_id, patientID) -- (either order)
Due to poor OR
optimization, it may be better to choose NULL or the empty string for communicate_with_pt
(to avoid testing both).