Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What is the fastest method to convert the following query:

SELECT COUNT(*) as c FROM tbl_fields
WHERE fieldnotes IS NULL
GROUP BY fieldno,fieldserial,id,fielddate,fieldsid 
HAVING COUNT(*) > 1;

... into one that will delete duplicated records? The table does not have any primary keys and contains several million entries.

share|improve this question
    
No primary key? ugh –  Derek Downey Dec 22 '11 at 15:35
    
No primary key, and "several million entries" –  BryceAtNetwork23 Dec 22 '11 at 15:46
    
Tell me about it :) –  tlvince Dec 22 '11 at 15:46
1  
Is there a field that is at least unique? –  BryceAtNetwork23 Dec 22 '11 at 16:00
1  
is it possible to add a column to the table and fill the column with a unique key then drop the column after the clean up? –  Lumpy Dec 22 '11 at 16:27

2 Answers 2

According to your query, you have fieldno,fieldserial,id,fielddate,fieldsid as a rule for uniqueness in the GROUP BY clause.

You can try this :

CREATE TABLE tbl_fields_unique LIKE tbl_fields;
ALTER TABLE tbl_fields_unique
ADD UNIQUE KEY unq (fieldno,fieldserial,id,fielddate,fieldsid);
INSERT IGNORE INTO tbl_fields_unique
SELECT * FROM tbl_fields;
ALTER TABLE tbl_fields RENAME tbl_fields_old;
ALTER TABLE tbl_fields_unique RENAME tbl_fields;

This will filter rows with duplicate fieldno,fieldserial,id,fielddate,fieldsid fields. Look over the new table. Once you are satisfied with the contents of the tbl_fields_unique table, do this:

ALTER TABLE tbl_fields RENAME tbl_fields_old;
ALTER TABLE tbl_fields_unique RENAME tbl_fields;

Give it a Try !!!

share|improve this answer
    
It seems to have worked and very quickly at that. However, using a sum on the count field in my query above, I get 3847, whereas your suggestion finds 5500 duplicates. I'll check the results and get back to you. Thanks. –  tlvince Dec 22 '11 at 23:08

If you can add an ID column and fill it with a unique value for each record then you should be able to run a query like

DELETE FROM tbl_fields
WHERE <New ID Column> IN (SELECT MAX(<New ID Column>)
                            FROM tbl_fields
                           WHERE fieldnotes IS NULL 
                          GROUP BY fieldno,fieldserial,id,fielddate,fieldsid  
                         HAVING COUNT(*) > 1)

This would remove the duplicates then you could delete the

If you have enough duplication that you exceed SQLs limitation for the IN statment then You could insert the ID values for the IN statement into a temp table and run an exists against that.

share|improve this answer
    
I added a new column using alter table tbl_fields add unq mediumint not null auto_increment key and left your suggestion running for ~3 hours before killing it. Even with a subset of the table it appeared to hang. Is this to be expected? –  tlvince Dec 22 '11 at 23:05
    
This won't work as MySQL does not allow to use the tables that is being deleted in a sub-select. –  a_horse_with_no_name Jul 19 '12 at 13:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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