I have inherited a MySQL table from one of my colleagues, and am in need of a little advice.

The table has the following structure:

| 0113313313 | 000001 |   YES    | 2003-01-01 19:00:00  | 9000001 | N/A  |

There is no primary key, as the table was not created with one. I cannot recreate the table as:

  1. It would take far too long to complete (over 2 million records and counting)
  2. This is a mission critical table, meaning taking it down would result in my client losing money for the duration.

I need to be able to remove duplicated rows from the table with a query, without inserting a primary key. "Duplicated" in this sense refers to all columns within the row being identical to another row.

EDIT This is the result of show create table query, as requested by ypercube;

CREATE TABLE `callerQue` (
  `PHONE_NO` varchar(30) DEFAULT NULL,
  `ACC_NUMBER` varchar(40) DEFAULT NULL,
  `GET_MAIL` varchar(6) DEFAULT NULL,
  `INT_ACC` varchar(40) DEFAULT NULL,
  `MAIL` varchar(50) DEFAULT NULL

1 Answer 1


Not sure if this would work in mysql since i am a MSSQL-dba but you could add an identity column -> auto_increment. This would make all rows unique. Then you could do a group by on duplicate records and select the highest ID for example. This way you will only delete one row. Repeat this until no more duplicates.

  • Thanks for the response! My question is, what would the overhead be for creating an auto-incremented column to a table with 4.5M rows? Commented Jun 12, 2015 at 7:32
  • After some persuasion, I have rebuilt the table with a PK now Commented Jun 12, 2015 at 10:22
  • How long did it take?
    – Rick James
    Commented Jun 12, 2015 at 22:28
  • 1
    Given the limited usable resources (needs an upgrade) it took around 20 minutes. 3 days if you count arguing the need for a primary key to the client. Commented Jun 15, 2015 at 7:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.