How to optimize IN constraint query?

Question

Following is my query.

SELECT * FROM t1 WHERE t1.record_id IN (
    SELECT  t2.record_id FROM  t2 
        INNER JOIN t3 ON CONCAT(t2.case_number,t2.courtfile_type) = CONCAT(t3.case_number,t3.courtfile_type))

It contain IN constraint which is taking lot of time to extract result from database.Database is huge obviously.

How can I optimize this query?

Answer 1

Try this:

USING JOIN

SELECT DISTINCT t1.* 
FROM t1 
INNER JOIN t2 ON t1.record_id = t2.record_id 
INNER JOIN t3 ON t2.case_number = t3.case_number AND t2.courtfile_type = t3.courtfile_type

USING EXISTS

SELECT * 
FROM t1 
WHERE EXISTS (SELECT t2.record_id 
                  FROM  t2 INNER JOIN t3 ON t2.case_number = t3.case_number AND t2.courtfile_type = t3.courtfile_type
                  WHERE t1.record_id = t2.record_id )

Check the execution plan of the query using EXPLAIN keyword and do proper indexing on tables.