CODEWITHSUNDEEP
mysqloptimization
Vidya
Vidya

Reputation: 8197

How to optimize this Mysql query

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 .

Upvotes: 0

Views: 54

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

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.

Upvotes: 3

Related Questions