2

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:

| PHONE_NO   | ACC_NO | GET_MAIL | DATETIME_CRE         | INT_ACC | MAIL |
| 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,
  `DATETIME_CRE` datetime DEFAULT NULL,
  `INT_ACC` varchar(40) DEFAULT NULL,
  `MAIL` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1'
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? – Michael Wiggins Jun 12 '15 at 7:32
  • After some persuasion, I have rebuilt the table with a PK now – Michael Wiggins Jun 12 '15 at 10:22
  • How long did it take? – Rick James Jun 12 '15 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. – Michael Wiggins Jun 15 '15 at 7:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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