Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    St.Petersburg, Russia
    Posts
    19

    Question Unanswered: Extremely slow executing of a query

    Hello!

    My client has a MySQL table with 38594 entries. And I need to remove all the duplicates, these duplicates have the equal value in the 'lyrics_file' field. So, to get a list of duplicates I execute this query:


    Code:
    SELECT l1.lyrics_id FROM lyrics AS l1, lyrics AS l2
    WHERE l1.lyrics_file = l2.lyrics_file AND l1.lyrics_id != l2.lyrics_id
    But it takes ages to permorm it. Is there any ways to get a list of duplicates or even to remove them by using only one query. Please, note that I shouldn't remove all lyrics with the same file, I should leave one, so if I have the following entries:

    Code:
    lyrics_id | lyrics_name | lyrics_file
    26274	 | Bringin' On the Heartbreak | Bringin-On-The-Heartbreak
    47485	 | Bringin On the Heartbreak  | Bringin-On-The-Heartbreak
    I should leave one of them, no matter which one.

    Thanks in advance!

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Extremely slow executing of a query

    Originally posted by Mike Borozdin
    Hello!

    My client has a MySQL table with 38594 entries. And I need to remove all the duplicates, these duplicates have the equal value in the 'lyrics_file' field. So, to get a list of duplicates I execute this query:


    Code:
    SELECT l1.lyrics_id FROM lyrics AS l1, lyrics AS l2
    WHERE l1.lyrics_file = l2.lyrics_file AND l1.lyrics_id != l2.lyrics_id
    But it takes ages to permorm it. Is there any ways to get a list of duplicates or even to remove them by using only one query. Please, note that I shouldn't remove all lyrics with the same file, I should leave one, so if I have the following entries:

    Code:
    lyrics_id | lyrics_name | lyrics_file
    26274	 | Bringin' On the Heartbreak | Bringin-On-The-Heartbreak
    47485	 | Bringin On the Heartbreak  | Bringin-On-The-Heartbreak
    I should leave one of them, no matter which one.

    Thanks in advance!
    Add an index on lyrics_file... What does the EXPLAIn of that query tell you anyway?

  3. #3
    Join Date
    Nov 2003
    Posts
    91

    Re: Extremely slow executing of a query

    Maybe this will be faster:

    select lyrics_id, count(lyrics_file)
    from lyrics
    group by lyrics_id
    having count(lyrics_file) > 1;

Posting Permissions

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