How to optimize "WHERE NOT IN" with subquery

Question

I have this query what was discussion output in this thread: Mysql JOIN subquery

See current fiddle: http://sqlfiddle.com/#!2/e97cf/22

create table c_contact 
(id INT, 
 name VARCHAR(20), 
 securityid INT
);

create table c_monitoring 
(started DATE, 
 ended DATE DEFAULT NULL, 
 securityid INT
);


SELECT
  c_contact.id,
  c_contact.name,
  c_contact.securityid
FROM c_contact
WHERE c_contact.securityid != ''
  AND c_contact.securityid NOT IN
      (select securityid 
       from c_monitoring 
       where ended is null 
       group by securityid
      )
GROUP BY c_contact.id ;

How the hell I am going to optimize this query? I have 100.000 records in c_contact table and about 10.000 in c_monitoring table. Query takes > 30 secs with 127 result rows.

EDIT: Case was solved by indexing tables correctly.

If you solved the problem, add the solution (the indexes used) as an answer. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jan 14, 2014 at 10:47

Bohemian · Accepted Answer · 2014-01-14 10:29:19Z

2

Your query has some group by problems (actually, you should not have any group by clauses), and you should convert it to a join:

SELECT
  c.id,
  c.name,
  c.securityid
FROM c_contact c
LEFT JOIN c_monitoring m ON m.securityid = c.securityid
  AND m.ended is null
WHERE c.securityid != ''
AND m.securityid IS NULL

See SQLFiddle

I also tidied up the query a little with aliases.

answered Jan 14, 2014 at 10:29

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bohemian Over a year ago

@AngularAddict Huh? I got 2 rows. Are you sure you've refreshed the page? This is the fiddle link: sqlfiddle.com/#!2/e97cf/34

makallio85 Over a year ago

Quess I had cached page. Works. Thanks for this.

makallio85 · Accepted Answer · 2014-01-14 10:53:41Z

0

Case was solved by indexing table fields

c_contact.securityid
c_monitoring.started
c_monitoring.ended
c_monitoring.securityid

Query takes now ~200ms

answered Jan 14, 2014 at 10:53

makallio85

1,3562 gold badges14 silver badges29 bronze badges

Collectives™ on Stack Overflow

How to optimize "WHERE NOT IN" with subquery

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related