[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:

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:

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!

[bstjean]

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:

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:

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?

[vanekl]

Maybe this will be faster:

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