I have a 700GB InnoDB table which I'm not writing any more data to (only reading).
I would like to delete the older data it holds and reclaim that disk space (as I'm running out of it). The delete part is pretty easy, because I have an auto-inc primary index so I can just iterate in chunks using it, and delete the rows, but that won't bring me back the space. I assume
OPTIMIZE TABLE will but that might take forever on a 700GB table, so is there another option I'm overlooking?
Edit by RolandoMySQLDBA
Assuming your table is
mydb.mytable, please run the following query and post it here so you can determine diskspace needed for the table's shrinkage:
SELECT FORMAT(dat/POWER(1024,3),2) datsize, FORMAT(ndx/POWER(1024,3),2) ndxsize, FORMAT((dat+ndx)/POWER(1024,3),2) tblsize FROM (SELECT data_length dat,index_length ndx FROM information_schema.tables WHERE table_schema='mydb' AND table_name='mytable') A;
We also need to see the table structure, if allowed.
Edit by Noam
This is the output of the query:
datsize ndxsize tblsize
682.51 47.57 730.08
This is the table structure (
SHOW CREATE TABLE)
`CREATE TABLE `mybigtable` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) NOT NULL, `created_at` datetime NOT NULL, `tid` bigint(20) NOT NULL, `text` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `ft` tinyint(1) NOT NULL, `irtsd` bigint(20) NOT NULL, `irtuid` int(11) NOT NULL, `rc` int(11) NOT NULL, `r` tinyint(1) NOT NULL, `e` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `timezone` varchar(5) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uid_tid` (`uid`,`tid`)) ENGINE=InnoDB AUTO_INCREMENT=2006963844 DEFAULT CHARSET=utf8`