Mysql query return too slow

Question

I have written a query. It works better. But currently, all tables have 100K rows, and one of my queries returns too slow. Can you please suggest to me how I can optimize the 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 here 'patientID' is varchar.

Duplicate of https://stackoverflow.com/questions/14167793/mysql-where-not-in-extremely-slow — rakarnik, May 31 '22 at 03:03
please edit your question to show (as text, not images) output of `show create table yourtablename` for all tables used in the query, and output of `explain select ...rest of your query` — ysth, May 31 '22 at 03:53
Does this answer your question? [MySQL WHERE NOT IN extremely slow](https://stackoverflow.com/questions/14167793/mysql-where-not-in-extremely-slow) — Ken White, Jun 01 '22 at 00:28

score 0 · Answer 1 · answered May 31 '22 at 05:38

This may run 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 help:

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

Since OR is poorly optimized, it may be better to pick either NULL or empty-string for communicate_with_pt (to avoid testing for both).

Mysql query return too slow

1 Answers1