How to make mysql table column as KEY, when already another column is set as PRIMARY KEY-CodePudding

In our mysql database, one table say "mytable" is having coumn mobile_no as primary key. But we are in need to make another column also as key. So that I can use that column in where condition. Show create of table is below-:

CREATE TABLE `report_data` (
  `api_request_id` int(11) NOT NULL AUTO_INCREMENT,
  `emailid` varchar(100) NOT NULL,
  `api_recipient_data` longtext,
  `request_params` varchar(255) DEFAULT NULL,
  `sent_date` date DEFAULT NULL,
  `sent_time` datetime NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1-processed, 2-success, 3-in-bounce, 4-invalid-domain,5-in-unsubscribe, 6-in-scrubbing',
  `api_userid` int(11) DEFAULT NULL,
  `domain_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`api_request_id`,`emailid`),
  KEY `sent_date` (`sent_date`)
);

I want to add one new column to this existing table and make that column as KEY.

CodePudding user response：

we want to add one column d_name and want it to make it as KEY

ALTER TABLE report_data 
    ADD COLUMN d_name {column definition} ,
    ADD INDEX (d_name);

sample fiddle