CODEWITHSUNDEEP
mysqlsql
Techlord
Techlord

Reputation: 91

"where not in (subquery)" very slow for a large table

I want to check whether cust_data_card_id exists or not in table tbl_cust_data , exists in table tbl_cust_dump_data.cust_data_card_id etc.

I wrote the following code

select * from tbl_cust_dump_data 
where tbl_cust_dump_data.cust_data_card_id NOT IN
      (select cust_data_card_id from tbl_cust_data);

When the query returns more than 18000 rows in tbl_cust_data it doesn't return any result, it only shows the loading for long time, but it is okay smaller sets of data

Upvotes: 2

Views: 2341

Answers (2)

Justin
Justin

Reputation: 9724

Try:

SELECT * 
FROM tbl_cust_dump_data t1
WHERE NOT EXISTS
      (SELECT 0 
       FROM tbl_cust_data t2
       WHERE t2.cust_data_card_id = t1.cust_data_card_id)

With this query you will be able to select rows from tbl_cust_dump_data, if cust_data_card_id doesn't exist in table tbl_cust_data.

Upvotes: 3

invisal
invisal

Reputation: 11171

I think there was a similar question asked not long ago >> HERE <<. Make sure you have cust_data_card_id indexed and please post the result of

EXPLAIN
select * from tbl_cust_dump_data 
where tbl_cust_dump_data.cust_data_card_id NOT IN
      (select cust_data_card_id from tbl_cust_data);

So that we can see what can be optimized further.

Upvotes: 0

Related Questions