Mysql查詢回傳太慢

Question

我已經寫了一個查詢。它效果更好。但目前，所有表都有 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。

Answer 1

這可能運行得更快：

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

這些索引可能有幫助：

tbl_location:  INDEX(CODE)
tbl_gxp_information:  INDEX(center_id, patientID)  -- (either order)

由於 OR 優化不佳，可能最好為 communicate_with_pt 選擇 NULL 或空字串（以避免對兩者進行測試）。