reclaim space from a table with foreign key ref
Posted by:
ashok r
Date: July 09, 2018 10:49PM
Here is my table def:
| obs_raw | CREATE TABLE `obs_raw` (
`obref` char(16) NOT NULL,
`location` varchar(100) NOT NULL DEFAULT '',
`state` varchar(100) NOT NULL DEFAULT '',
`region` varchar(50) NOT NULL,
`location_raw` varchar(100) DEFAULT '',
`state_raw` varchar(100) DEFAULT '',
`region_raw` varchar(50) DEFAULT NULL,
`salary_raw` varchar(100) DEFAULT '',
`post_date_raw` char(100) DEFAULT NULL,
`expiry_date_raw` char(100) DEFAULT NULL,
`last_update` datetime NOT NULL,
KEY `obs_raw_obs1` (`obref`),
CONSTRAINT `FK_obs_raw_obs1` FOREIGN KEY (`obref`) REFERENCES `obs` (`obref`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT |
I have deleted a lot of unwanted data from this table. Table size shows 33GB.
I took a mysqldump of this table and I can see it has only 7GB.
Now, How do I optimise this table online so I can reclaim disk space?
pt-osc - does not support as I do not have primary key in this table and also it has FK ref!!!
I'm using mysql 5.5 version..
Please advise best way to follow. Thanks,