To remove duplicate rows in a table I did the following (about 200k rows)
table1: 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 ;
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?