To remove duplicate rows in a table I did the following (about 200k rows)


id_a int
id_b int
val_a int
val_b int
pk (id_a, id_b), index id_a, index id_b


created table2 as select from table1;
drop table1;
created table1 as select from table2 (with removed dublicates);
created indexes for table1;
drop table2 ;

After that, mysqld started to use 40-140% CPU at VPS with any query that uses that table (0% idle). Strange, that CPU usage increased even if I query another tables, that were not changed. It all started after those changes.

SHOW PROCESSLIST shows only "sleep" and "show processlist".

I've tried to run mysqlcheck and tried to restart mysql-server - no errors are reported.

I enabled slow query logs, and they appeared there. Even those, which ran fast one day ago.

What could be wrong and what else can I do to diagnose the cause?

  • 1
    What storage engine you're using (InnoDB, MySQL), if InnoDB are you using innodb_file_per_table option? Give as more information.
    – ludesign
    Commented Sep 5, 2011 at 23:22
  • It could be that loading the data into a new non-temporary table filled up your database on disk and then once you removed it you've left mysqld with space to garbage collect and reuse, and that's what's taking the CPU? Why not instead build t2 as a temporary table of the duplicates and then use that to delete rows from t1 in-place without destroying and rebuilding the table?
    – Rup
    Commented Sep 5, 2011 at 23:23
  • MyISAM engine. Of course, I could do that with tmp tables, but that's not a problem. The problem is how to solve that issue. I thought about garbage collector, but idle 0% and I restarted service few times. Also I tried to use another database from original dump - same problem. After 8 hours it started to increase only to 15-40%, but it still isn't normal.
    – Wayne
    Commented Sep 6, 2011 at 5:03

1 Answer 1


First thing I noticed is that you have a redundant index.

This is your original table1:

id_a int
id_b int
val_a int
val_b int
pk (id_a, id_b), index id_a, index id_b

You do not need the index on id_a since it is already the first column of the primary key. That's 200k rows less of index entries to process. Just run this to get rid of it:


Another step you will need is to reload the table and disable non-unique indexes while reloading (This is MyISAM only). In addition, you need a cleaner algorithm. I have two algorithms. Here is the first one:


CREATE TABLE table2 LIKE table1;
INSERT INTO table2 SELECT * FROM table1;
ALTER TABLE table1 RENAME table0;
ALTER TABLE table2 RENAME table1;
DROP TABLE table0;

Since DISABLE KEYS leave the primary key open for updating, the primary key is loaded during the INSERT INTO table2. The ENABLE KEYS step rebuilds the id_b index.

Now, here is the second algorithm:



A whole lot shorter, isn't it ? The second algorithm (using OPTIMIZE TABLE) does everything the first did plus recompute index statistics, do internal table repairs, and index reorganization.

UPDATE 2011-09-07 17:38 EDT

The reason these two algorithms should improve things is the fact MySQL indexes are now processed linearly.

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.