My existing project has data of about 140million records in Contentq table. And I have planned to rotate the table just incase not to face any load on the table. So I have planned to rotate data older than 2 months to another table contentq_old and rename current latest 2 months data to contentq1 and create a structure only table contentq. As I have only updates/deletes for the latest inserted records I can take that risk. Let the application to point to empty table contentq for its operation. As there were almost 80million records deleted I should be going for rebuild indexes on contentq1 that has latest 2 months records. Once rebuilt done lock table contentq and do -
"insert into contentq1 select * from contentq';" .
Then do drop contentq and rename contentq1 to contentq.
My problem is no down time for application hits, but I can park for somewhile. I should be knowing how long would the rebuild go on a latest 2months record that holds 50 million records max. Below is the structure of the table contentq. But how can I estimate how long an index rebuild would take?
Mysql 5.0.77 on 16GB RAM
contentq | CREATE TABLE `contentq` ( `idcontent` bigint(120) unsigned NOT NULL auto_increment, `keyvendor` varchar(32) default '0', `msisdn` varchar(16) default NULL, `cli` varchar(16) default NULL, `message` text, `udh` text, `priority` int(10) unsigned default '1', `recorddate` timestamp NOT NULL default CURRENT_TIMESTAMP, `callback_url` text, `transactionid` varchar(256) default NULL, `status` tinyint(4) default '0', PRIMARY KEY (`idcontent`), KEY `PriorityIndex` (`status`,`priority`,`keyvendor`), KEY `NewIndex1` (`keyvendor`,`status`), KEY `idx_contentq_msisdn` (`msisdn`) ) ENGINE=InnoDB AUTO_INCREMENT=136583422 DEFAULT CHARSET=latin1
Please advise. Thanks!!