Mysql query returns too slowly

Question

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.

Answer 1

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).