Extremely slow executing of a query

Mike Borozdin · #1 (**permalink**) 12-01-03, 12:39

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!

bstjean · #2 (**permalink**) 12-01-03, 14:24

Quote:

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?