Redesign query without using IN statement

Question

I have a query like below. This query is working fine but since I have huge amount of data to check, this query running is slower.

Can anybody help me how to optimize this query?

SELECT * from trn where concat(comp_id,cus_id) 
IN (select concat(comp_id,cus_id) FROM cus where sid='A0001') 
AND docdate between ('2018-01-01') and ('2018-04-30')

Answer 1

The concat is probably causing those performance problems as it can't use indexes.

But MySQL supports multi-column subqueries, so simply remove it:

SELECT * from trn 
where (comp_id,cus_id) IN 
        ( select comp_id,cus_id
          FROM cus 
          where sid='A0001'
        ) 
  AND docdate between ('2018-01-01') and ('2018-04-30')

Answer 2

You can use JOIN, e.g.:

SELECT DISTINCT t.*
FROM trn t 
JOIN cus c ON t.comp_id = c.comp_id AND t.cus_id = c.cus_id
WHERE c.sid='A0001' AND t.docdate BETWEEN ('2018-01-01') AND ('2018-04-30');