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 或空字符串（以避免对两者进行测试）。