CODEWITHSUNDEEP
mysql
Sergey
Sergey

Reputation: 3

MySQL, 'WHERE NOT IN' slow

I'm having issues with MySQL query running extremely slow. It takes more than 10 min for each one. I've tried to rewrite it using LEFT JOINT, but "joints" are not my best expertise ))

I hope someone will be able to help me.

    select subscriberId 
    from segments 
    where (segmentId = 29) 
    and subscriberId not in 
    (select subscriberId from que where (campaignId = 31 or campaignId = 32)) 
    order by rand() 
    limit 20000

All indexes are in place. Thanks a lot in advance.

Upvotes: 0

Views: 144

Answers (3)

BaBL86
BaBL86

Reputation: 2622

I think, your problem is in

order by rand()

try to delete this and look time.

UPDATE

  1. add index:

    alter table que add index (subscriberId)

  2. try this:

    select segments.subscriberId from segments left join que on que.subscriberId = segments.subscriberId and ( que.campaignId = 31 or que.campaignId = 32 ) where segments.segmentId = 29 and que.id is null limit 20000

I see 0.08 sec and 0.13 with order by rand().

Don't use phpmyadmin to check, because of it have more time to render result. I'm using console mysql client

20000 rows in set (0.13 sec)

mysql> select segments.subscriberId from segments left join que on que.subscriberId = segments.subscriberId and ( que.campaignId = 31 or que.campaignId = 32 ) where segments.segmentId = 29 and que.id is null order by rand() limit 20000 ;

Upvotes: 0

Kapil gopinath
Kapil gopinath

Reputation: 1053

Try using a join

SELECT sg.subscriberId 
    FROM segments sg JOIN que q 
    ON (sg.subscriberId = q.subscriberId and sg.segmentId = 29)
    WHERE (q.campaignId <> 31 AND q.campaignId <> 32)

Upvotes: 1

slavoo
slavoo

Reputation: 6076

Try to replace NOT IN with NOT EXISTS

select s.subscriberId 
from segments s
where (s.segmentId = 29) 
and NOT EXISTS (select * from que q 
                where s.subscriberId = q.subscriberId AND 
                (q.campaignId = 31 or q.campaignId = 32)) 
order by rand() 
limit 20000

Upvotes: 1

Related Questions