I have two tables in MySQL 8, table A with primary key and table B with a foreign key of the same type on column 1. table B has an index on column 1. If I want to change the index on table/column B1, is it safe to create a new index on the column (B1), then delete the old index?

By "change index" I want to say, add another column and sort order to the index. Like:

create index b1_b2_IDX on b (b1 DESC, b2 ASC) ;
  • What do you mean with "change the index"? – ypercubeᵀᴹ Jul 12 '18 at 20:27
  • What do you mean by "safe"? If you're asking if any data from the table can get lost when deleting an index: No (unless there is an unlikely bug in the DBMS of course). – sticky bit Jul 12 '18 at 20:35
  • By "change index" I want to say, add another column and sort order to the index. like `create index b1_b2_IDX on b (1 DESC, 2 ASC) . – amaurer Jul 12 '18 at 21:01
  • By "safe" I want to know if I'll mess up the relationship with the primary/foreign key constraint by swapping indexes on column B1 – amaurer Jul 12 '18 at 21:02
  • Can you post the code (CREATE TABLE statements and how you want to change the index)? – ypercubeᵀᴹ Jul 12 '18 at 21:31

Yes, you can do this in 2 statements. The first adding the new index and the second dropping the old one. If you tried the reverse order, MySQL will complain because the FOREIGN KEY constraint requires an index.

Or - even better - with a single statement, that drops the existing index and adds the new one:

alter table b
  drop index b1_idx,
  add index b1_b2_idx 
    (b1 desc, b2 asc) ;

Test at sqlfiddle.com.

| improve this answer | |
  • Cool. So to confirm, by changing the index like this, it will not have an effect on the foreign key? – amaurer Jul 13 '18 at 11:20
  • No, the FK will have no problem. The new index will be used from the change and after, for enforcing the FK. – ypercubeᵀᴹ Jul 13 '18 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.