I know that optimize table is supposed to take quite a long time, but it has now been around 10 hours and I'm still waiting for it to finish.
The table in question stores logs. It had around 17 million records. I deleted just over half of them because disk space was starting to become an issue. I then ran optimize table. It's on a fairly powerful EC2 server so I didn't expect it to take more than a hour or two. It's still going but the CPU and disk usage on the server are very low so I'm starting to wonder if it's actually doing anything. Although space was getting low, there is still about 10gigs left. It gobbled up about another gig or so when I first ran it.
The processlist shows this:
| 235589450 | root | localhost | <the db> | Query | 38210 | Repair by sorting | optimize table <the table>
The table definition is:
CREATE TABLE `<table name>` (
`<field 1>` int(16) NOT NULL AUTO_INCREMENT,
`<field 2>` int(8) NOT NULL DEFAULT '0',
`<field 3>` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`<field 4>` varchar(255) NOT NULL DEFAULT '',
`<field 5>` varchar(15) NOT NULL DEFAULT '',
`<field 6>` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`<field 1>`),
KEY `<key 2>` (`<field 2>`,`<field 3>`),
KEY `<key 3>` (`<field 2>`,`<field 4>`,`<field 3>`),
KEY `<key 4>` (`<field 5>`,`<field 3>`)
) ENGINE=MyISAM AUTO_INCREMENT=17485259 DEFAULT CHARSET=latin1
Any advice would be gratefully received.