fetch the records in one table which is not exist in another table using mysql
I am trying to fetch the records in one table which is not exist in another table using mysql. It is having 1 to many relationship.
I am working with approx. 50,000 numbers of data.I have tried it using two queries but when I fire queries, my database hangs and website crashes. Please help. Below is my query.
SELECT DISTINCT a.id FROM abc as a WHERE a.id NOT IN (SELECT b.a_id FROM xyz as b)
SELECT DISTINCT a.id from abc as a LEFT OUTER JOIN xyz as b on (a.id=b.a_id) where b.a_id is null
Make sure you have indexes on a.id and b.a_id.
I don't know if it performs any better or not, but I've also seen EXISTS used:
SELECT DISTINCT a.id FROM abc AS a WHERE NOT EXISTS (SELECT * FROM xyz where a_id = a.id)
Last edited by NogDog; 08-28-2013 at 03:45 PM.
Thanks for your reply, I tried it but this query also hangs my database.
Did you make sure you have indexes on all columns being used in any of the WHERE clauses?
Otherwise, it's probably time to use EXPLAIN to find where the bottleneck is.
Thank you sooooooooooo much @NogDog, after indexing I got the result easily. It worked for me
Thank you again
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread