mysql where not in to left outer join

Question

I have the following query and would like to convert it to using a left outer join instead of a not in to see if it would run faster that way. It's currently taking this query about 40 seconds to run on our database. I'm not familiar enough with using outer joins for this type of thing to convert it myself.

select
    c.contact_id as contact_id,
    c.orgid as organization_id,
    c.first_name as first_name,
    c.last_name as last_name,
    a.address_state as state
from cnx_contact as c
inner join cnx_address as a on c.home_address_uid = a.address_uid
where a.address_state = 'OH'
and (c.orgid = 45 or c.orgid = 55)
and c.contact_id NOT IN (
    select pc.contact_id
    from cnx_contact as c
    inner join cnx_address as a on c.home_address_uid = a.address_uid
    inner join cnx_contact_group_participant as gp on c.contact_id = gp.contact_id
    inner join cnx_contact_participant_role as cr on gp.participant_role_uid = cr.participant_role_uid
    inner join cnx_contact_group as cg on gp.group_uid = cg.group_uid
    inner join cnx_contact_group_participant as pgp on cg.primary_participant_uid = pgp.participant_uid
    inner join cnx_contact as pc on pgp.contact_id = pc.contact_id
    where (c.orgid = 45 or c.orgid = 55)
    and   cr.name = 'Applicant'
);

Someone correct me if I'm wrong, but I don't think `NOT IN` and `LEFT JOIN` do the same thing... — AdamMc331, May 29 '15 at 19:20
They don't do the same thing, but you can use outer joins in a lot of cases as a more efficient way to do this type of filtering. It may not work for this query, not sure though. — homebrewerluke, May 29 '15 at 19:22
Well I think you'd have to do a left join, and only select where the joined column is null (meaning there wasn't a relationship) but I'm not sure if it would be any faster. — AdamMc331, May 29 '15 at 19:22
This might be relevant to you: http://stackoverflow.com/questions/6777910/sql-performance-on-left-outer-join-vs-not-exists — AdamMc331, May 29 '15 at 19:26

score 0 · Answer 1 · answered May 30 '15 at 08:52

select
    c.columns
from cnx_contact as c
inner join cnx_address as a on c.home_address_uid = a.address_uid

LEFT JOIN 
(Subquery goes here) x
ON x.contact _id = c.contact_id
where a.participant_state = 'OH'
and c.orgid IN(45,55)
and x.contact_id IS NULL;

mysql where not in to left outer join

1 Answers1