Better Way To Run This Query? Checking For Dupes.

[Codexer]

Hey guys,

 

First of all, wow! How did I not know about this forum? Unbelievable amount of knowledge here.

 

Now I have a question regarding MySQL. I am trying to sort duplicate rows based on two fields from a table. This table has several million rows. Currently, I have the following:

 

 

SELECT p1.id AS idone, p1.duration AS durationone, p1.title AS titleone, p1.status, p2.id AS idtwo, p2.duration AS durationtwo, p2.title AS titletwo, p2.status
FROM table_one AS p1, table_one AS p2
WHERE p1.title = p2.title AND p1.duration = p2.duration AND p1.status != 0 AND p2.status != 0 AND  p1.id != p2.id
LIMIT 20

 

Output:

idone  |  durationone   |  titleone  | idtwo  |  durationtwo  |  titletwo  

 

Which will return rows which have the same title AND duration where the ID is different plus the status is not 0... The problem is, that it's quite slow. Especially when I want to return 50+ rows it reaches over 60 seconds. Ideally, I would like the query to be fast enough to return about 100 duplicate rows at a time for further action.

 

Is there a better way of doing this? I believe it's the "AND"s that are slowing it down. As if you trim them back down, the query gets a lot faster. However, it also defeats the purpose. I would be fine with creating a temporary index for this task. I do realize that methods can be put into place to prevent this in the first place however it needs some human intervention.

 

If you guys have any ideas on a better/faster way to retrieve this information, I would be highly appreciative!

[Barand]

do you have indexes on title, duration, status?

[Codexer]

I've got the primary on ID, however there's no index on title, duration or status. Should I create them as "one" index? Or individual? I will only leave the indices there whilst I perform this task, as the table is heavy on inserts.

 

Cheers!

[gizmola]

One index on title, duration, status, id.

[fenway]

And show EXPLAIN before and after.