www.webdeveloper.com
Results 1 to 5 of 5

Thread: fetch the records in one table which is not exist in another table using mysql

  1. #1
    Join Date
    May 2013
    Posts
    11

    Question 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)

    OR

    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

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,604
    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:
    Code:
    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 04:45 PM.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    May 2013
    Posts
    11
    Thanks for your reply, I tried it but this query also hangs my database.

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,604
    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.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    May 2013
    Posts
    11
    Thank you sooooooooooo much @NogDog, after indexing I got the result easily. It worked for me
    Thank you again

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles