I have a following table with 1 million records :
CREATE TABLE `activity` ( `id` int(9) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL, `subscription_id` int(10) unsigned NOT NULL, `vid` int(10) unsigned NOT NULL, `action` enum('init','confirm','suspend') DEFAULT NULL, `source` varchar(20) DEFAULT NULL, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `num` varchar(32) NOT NULL, `model` varchar(20) DEFAULT NULL, `price` float(5,2) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `num` (`num`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
I want to delete entries for same
num value but that are old. So I came up with following query :
delete activity from activity inner join ( select max(id) as maxid, num from activity group by num having count(*) > 1) duplic on duplic.num = activity.num where activity.id < duplic.maxid;
The select query works fine, returns around 400.000 records. But if I try delete, the query executes infinitely. What might be wrong with this? How to investigate?