MySQL Forums :: Performance :: Multiple inner join Query with NOT IN takes forever to execute for large resultset

Hello,

I have the following two queries on production server:

1) select count(*) from segmentation_cycle_recipients scr, segmentation_instance si where si.access_code=scr.access_code and si.segment_id is NOT NULL;

Returns 13429 rows in 0.2 secs

2)select count(*) from segmentation_cycle_recipients scr, segmentation_instance si, web_pat_info wpi where si.access_code=scr.access_code and scr.siebel_row_id=wpi.siebel_id and si.segment_id is NOT NULL;

Returns 4003 rows in 0.48 secs

Now, I want 1)-2) So I wrote the following query:

select count(*) from segmentation_cycle_recipients scr, segmentation_instance si where si.access_code=scr.access_code and si.segment_id is NOT NULL and scr.siebel_row_id NOT IN (select scr.siebel_row_id from segmentation_cycle_recipients scr, segmentation_instance si, web_pat_info wpi where si.access_code=scr.access_code and scr.siebel_row_id=wpi.siebel_id and si.segment_id is NOT NULL);

expecting 13429-4003=9426 rows but the query takes forever (have to kill the query command) to execute. It even adds a counter in the "slow queries" listing in mysql>status;)

It works in development environment where the result set is much smaller. So I believe the query itself is right.

What could be possibly wrong in the production db?



Edited 1 time(s). Last edit at 11/09/2009 10:43AM by infinity heaven.