Aditya Kokil Aditya Kokil -4 years ago 129
MySQL Question

MySQL NOT IN query taking too long to respond

The Query takes too long to respond ~ 40 minutes.

`SELECT c.consumer_id FROM consumer c
WHERE c.active_flag = 'Y'
AND ( c.frequency = 'Q' )
AND c.consumer_id NOT IN(
SELECT consumer_id
FROM (SELECT s.consumer_id
FROM transactions s
WHERE ( s.cycle='2016-Q-2' ) and s.active_flag = 'Y' AND s.status <> 'Door Locked')
AS subquery)`


I've also tried with NOT EXISTS and LEFT JOIN / IS NULL version of above query same time for both of them.

consumer
table have


  • consumer_id varchar(12)

  • active_flag varchar(6)

  • frequency varchar (2)

  • 130000 Rows with where clause

  • Total Rows 160000

  • unique index on consumer_id

  • index on active_flag



transaction
table have


  • consumer_id varchar(12)

  • active_flag varchar(6)

  • status varchar (20)

  • cycle varchar (13)

  • 108000 Rows with where clause

  • Total Rows 270000

  • index on consumer_id status and cycle



Server config

-16GB RAM
-8 Core Intel(R) Xeon(R) CPU E5-4640 v2 @ 2.20GHz
-MySQL 5.6.35

Explain return
SQL QUERY EXPLAIN

I hope this helps.
Thanks in advance.

UPDATE 1

consumer and transactions have one-to-many relation
so consumer_id will repeat for every cycle.

Answer Source

Inner Join did the trick for me

SELECT DISTINCT c.consumer_id FROM consumer c
INNER JOIN
    (SELECT DISTINCT consumer_id as sid from transactions where consumer_id not in (
  select consumer_id from transactions
  where cycle = '2016-Q-2' AND active_flag = 'Y' AND status != 'Door Locked'
)) as s
ON s.sid = c.consumer_id
WHERE  c.active_flag = 'Y' AND ( c.frequency = 'Q' )

not sure if this the correct way but response time is down to ~ 700ms now.

not sure why but the response of queries from all the above answers gave all the consumer ids available in the transaction table.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download