Skip navigation links

MySQL Forums :: InnoDB :: How long would a delete take for 50 million records in MySQL with the InnoDB engine?


Advanced Search

How long would a delete take for 50 million records in MySQL with the InnoDB engine?
Posted by: Mannoj Kumar ()
Date: March 15, 2013 12:37AM

How long would a delete take for 50 million records in MySQL with the InnoDB engine?

Server Config:

MySQL Version 5.0.77
CentOS 5.4 Final
64 bit 16GB RAM
RAID 5
Processor Model : Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
4 cores with 16 processors
DB Hits

127.59 inserts/s
142.39 updates/s
169.28 deletes/s
967.70 reads/s
Note: innodb_buffer_pool_size set to 12G, innodb_per_table is set to NO.

CREATE TABLE `contentq_old` (
`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=137977141 DEFAULT CHARSET=latin1;


select count(*) from contentq_old where idcontent > 82072064 ; ==> 55905077

delete from contentq_old where idcontent > 82072064 ;
Can someone let me know how much time would it take to complete? Its almost 4 hrs and 45mins now.

Also I'd like to understand how MySQL delete architecture works. Does it delete and recreate the table for indexes to be kept in place and thus it frees space in the disk?

Options: ReplyQuote


Subject Views Written By Posted
How long would a delete take for 50 million records in MySQL with the InnoDB engine? 1180 Mannoj Kumar 03/15/2013 12:37AM
Re: How long would a delete take for 50 million records in MySQL with the InnoDB engine? 846 Rick James 03/15/2013 11:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.