We have a fairly unoptimized table with the following definition:
CREATE TABLE `Usage` (
`TxnDate` varchar(30) DEFAULT NULL,
`TxnID` decimal(13,2) NOT NULL,
`UserID2015` varchar(20) DEFAULT NULL,
`UserRMN` decimal(13,0) DEFAULT NULL,
`CustomerNo` decimal(13,0) DEFAULT NULL,
`OperatorName` varchar(50) DEFAULT NULL,
`AggregatorName` varchar(30) DEFAULT NULL,
`TransAmount` decimal(10,2) DEFAULT NULL,
`MMPLTxnID` decimal(13,0) DEFAULT NULL,
`ProductType` varchar(30) DEFAULT NULL,
`YearMonthRMN` varchar(50) DEFAULT NULL,
PRIMARY KEY (`TxnID`),
UNIQUE KEY `TxnID` (`TxnID`) USING BTREE,
KEY `TxnDate` (`TxnDate`),
KEY `OperatorName` (`OperatorName`),
KEY `AggregatorName` (`AggregatorName`),
KEY `MMPLTxnID` (`MMPLTxnID`),
KEY `ProductType` (`ProductType`),
KEY `UserRMN` (`UserRMN`),
KEY `YearMonthRMN` (`YearMonthRMN`) USING BTREE,
KEY `CustomerNo` (`CustomerNo`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
You're not just dropping an index, you're dropping the primary key.
Normally, InnoDB tables are stored as a clustered index based on the primary key, so by dropping the primary key, it has to create a new table that uses either the secondary unique key or else an auto-generated key for its clustered index.
I've done a fair amount of MySQL consulting, and the question of "how much time will this take?" is a common question.
It takes as long as it takes to build a new clustered index on your server. This is hard to predict. It depends on several factors, like how fast your server's CPUs are, how fast your storage is, and how much other load is going on concurrently, competing for CPU and I/O bandwidth.
In other words, in my experience, it's not possible to predict how long it will take.
Your table will be rebuilt with
TxnID as the new clustered index, which is coincidentally the same as the primary key. But apparently MySQL Server doesn't recognize this special case as one that can use the shortcut of doing an inplace alter.
Your table also has eight other secondary indexes, five of which are varchars. It has to build those indexes during the table restructure. That's a lot of I/O to build those indexes in addition to the clustered index. That's likely what's taking so much time.
You'll go through a similar process when you add your new auto-increment primary key. You could have saved some time if you had dropped your old primary key and created the new auto-increment primary key in one ALTER TABLE statement.