Removing InnoDB indexes causes database table almost not accessible and slow I have a server with 32GB RAM. I have given almost 70% InnoDB buffer pool to it. I have table with almost 40 million rows. Recently, I tried to remove one of the indexes and replace it with another. My whole system could not work any more and it was not able to see my db.

I could see a lot of this error in my application:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
No operations allowed after statement closed.


Also I saw a lot of too "many file open" errors on my application due to this process.

I had to stop the database, truncate the table, and start afresh. Is this an InnoDB limitation? In future, what can I do to avoid this? What other limitations do I need to be aware of?

Is it not advisable to remove indexes when the size reaches big as few millions like this?

Is it a good reason for my to swap to TokuDB?

Below is my table description:

CREATE TABLE IF NOT EXISTS `mainData` (
  `mainDataID` int(11) NOT NULL AUTO_INCREMENT,  
  `deviceID` int(11) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `gpdateTime` datetime NOT NULL,   
  `linkID` int(5) NOT NULL DEFAULT '0',
  `eventID` int(11) NOT NULL DEFAULT '0',
  `dataInsertDateTime` datetime NOT NULL, 
  PRIMARY KEY (`mainDataID`,`dataInsertDateTime`),
  KEY `gpdateTime` (`gpdateTime`),
  KEY `linkID` (`linkID`),
  KEY `eventID` (`eventID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=50425561 ;


This was on a running production system with heavy inserts going in so that is why all almost halted. When tables reach a huge sum in million how to best to say remove and replace a new index?

My version is 5.1.73. I know kind of old; as you know CentOS goes for stability. What I did was removing the gpdateTime index via PHPMyAdmin and everything started to fall apart. My OS which I am running is Centos 6.8.
5.1 ALTERs work faster if you perform them simultaneously:

ALTER TABLE mainData
    DROP INDEX foo,
    ADD INDEX (col1, col2);


You can perform that in the UI window that lets you perform arbitrary SQL.  It will be twice as fast (in older versions) than doing the DROP and ADD separately.  This is because each ALTER (not each clause in it) involves a complete copy of the data, plus a complete rebuild of the resulting set of indexes.