How to optimize this Mysql query

Question

Need guidance on Query optimizations. I have two tables userEvents and events

userEvents
id |  userName | eventID

events
id |  name

I need to find all the events name which has not eventID in userEvents

select id, name
from  events
where id NOT IN ( select eventid
                  from userEvents
                  inner join events on events.id = userEvents.eventid
                  group by userEvents.eventID
                )

I am able to get the result .I want to know how can I OPTIMIZE this query since there are large number of rows .

Mahmoud Gamal · Accepted Answer · 2013-05-16 07:48:55Z

3

You can use LEFT JOIN:

SELECT e.*
FROM events AS e
LEFT JOIN userEvents AS v ON e.id = v.eventid
WHERE e.id IS NULL;

Or: remove the INNER JOIN inside the subquery, you didn't need it:

select id, name 
from  events 
where id NOT IN (select eventid 
                 from userEvents 
                 where eventid IS NOT NULL)

Note that: the predicate IN is not safe with the NULL values coming from the eventid column, thats why I added eventid IS NOT NULL to the subquery. LEFT JOIN will be safer in this case.

edited May 16, 2013 at 7:48

answered May 16, 2013 at 7:43

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

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

1 Comment

Vidya Over a year ago

where condition should be e.eventID is null

Collectives™ on Stack Overflow

How to optimize this Mysql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related