0

Am trying to get the all rows from Tabl1 which are not available in Table2 with help of NOT IN MSQL query. But am getting timeout exception and query is not getting executed. Below is the mysql query which I am using.

    SELECT * FROM identity WHERE
  unique_id NOT IN (SELECT Message_Queue.index FROM Message_Queue);

Could any please tell the reason or any other way for replacement of NOT IN operation?

jaczes
  • 1,366
  • 2
  • 8
  • 16
Pand005
  • 1,095
  • 3
  • 23
  • 53
  • Can you please give feedback if the answers provided helped/solved your issue? You can also mark one answer as working solution by clicking the checkmark under the answer score. Maybe you like to revisit your previous questions also. – juergen d Sep 12 '13 at 16:45

2 Answers2

0

Because in MySQL NOT IN is less performant, try using EXISTS

SELECT  * 
FROM    identity a
WHERE   NOT EXISTS
        (
            SELECT  null
            FROM    Message_Queue b
            WHERE   b.index = a.unique_id 
        );

you should also put an index on those columns.

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Above script took 442.279 secs for finding missing rows from 100 millions records. With the below answer it took 368.630 secs. Thanks for the reply. – Pand005 Sep 16 '13 at 09:34
0

When you have so many records in the in() clause then you should use a join instead

SELECT t1.* 
FROM table1 t1
left join table2 t2 on t2.myId = t1.myId 
where t2.myId is null
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • Above script took 368.630 secs for finding missing rows from 100 millions records. Compared to first answer this one speed is high. Thanks – Pand005 Sep 16 '13 at 09:33