CODEWITHSUNDEEP
mysqlindexing
user4757174
user4757174

Reputation: 426

Add column to existing multi-column index

In my table I have a multi-column index for the columns (name,folder)

I recently added a new column called date and I want to put an index into it, but I want to put it into the existing multi-column index

When I do Alter table books add index theindex (date); I get Duplicate key name 'theindex'

How do I add another column to an index without creating a new key?

Upvotes: 16

Views: 17556

Answers (1)

Praveen Kumar Purushothaman
Praveen Kumar Purushothaman

Reputation: 167172

Just change the syntax to the below way to add new columns to the index:

ALTER table `books` DROP INDEX theindex;
ALTER table `books` ADD INDEX theindex (`date`, `time`);

Be cautious about FOREIGN KEYs. See, for example, https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

Upvotes: 25

Related Questions