SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    844
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Tackling a table with duplicates using LIMIT

    The logins table has over 400,000 rows. The query below was timing out before it could ever finish. So I added the LIMIT 100, to see if that would help. It didn't, as well as it didn't help when I moved the LIMIT 100 after the last closing parenthesis. By the way, this same type of query worked great on some other tables. Any thoughts?

    Thanks!


    #Create the temporary table to store the IDs of the elements to erase.
    CREATE TEMPORARY TABLE ids (loginID int);


    #Find the IDs of the elements to erase:
    INSERT INTO ids(loginID) SELECT loginID FROM logins AS t
    WHERE 1 != (SELECT COUNT(*) FROM logins
    WHERE logins.loginID <= t.loginID
    AND logins.uID = t.uID
    AND logins.tempDate = t.tempDate LIMIT 100);


    #Delete the elements of the table with the previously selected indexes
    DELETE logins FROM logins,ids WHERE logins.loginID = ids.loginID;


    #Remove the temporary table
    DROP TABLE ids;
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by busboy View Post
    The logins table has over 400,000 rows. The query below was timing out before it could ever finish. So I added the LIMIT 100, to see if that would help. It didn't, as well as it didn't help when I moved the LIMIT 100 after the last closing parenthesis. By the way, this same type of query worked great on some other tables. Any thoughts?

    Thanks!


    #Create the temporary table to store the IDs of the elements to erase.
    CREATE TEMPORARY TABLE ids (loginID int);


    #Find the IDs of the elements to erase:
    INSERT INTO ids(loginID) SELECT loginID FROM logins AS t
    WHERE 1 != (SELECT COUNT(*) FROM logins
    WHERE logins.loginID <= t.loginID
    AND logins.uID = t.uID
    AND logins.tempDate = t.tempDate LIMIT 100);


    #Delete the elements of the table with the previously selected indexes
    DELETE logins FROM logins,ids WHERE logins.loginID = ids.loginID;


    #Remove the temporary table
    DROP TABLE ids;
    End of the day and I'm slipping... 400k records is not enough to constitute a freeze unless somethings being written wrong or an index isn't being hit. What constitutes a dupe? uID and TempDate?

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    844
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I forgot the index and all is working fine now. Thank you.
    Convert your dollars into silver coins. www.convert2silver.com


Bookmarks

Posting Permissions

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