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.

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
– ysth, Commented May 31, 2022 at 3:53
Does this answer your question? MySQL WHERE NOT IN extremely slow — Ken White
– Ken White, Commented Jun 1, 2022 at 0:28

Rick James · Accepted Answer · 2022-05-31 05:38:19Z

0

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

answered May 31, 2022 at 5:38

Rick James

144k15 gold badges144 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql query return too slow

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related