CODEWITHSUNDEEP
mysql
Mike Baranczak
Mike Baranczak

Reputation: 8374

Adding unique constraint to large MySQL table

I have an InnoDB table on MySQL 5.6, and I'd like to add a unique constraint to a VARCHAR column (let's call it column x). Pretty simple, except that I have over 5 million rows, so I want to anticipate any problems before I start.

Specific questions:

  1. Ballpark estimate for how long this will take?
  2. I plan to remove duplicate values for x before I start. But if someone tries to insert a non-unique value for x while the ALTER operation to add the constraint is in progress, what will happen?
  3. Is there any difference between ADD UNIQUE, ADD CONSTRAINT UNIQUE, ADD UNIQUE INDEX?

Thanks in advance.

Upvotes: 2

Views: 2362

Answers (2)

H4F
H4F

Reputation: 91

use ghost migration if you have replica

Upvotes: 0

tadman
tadman

Reputation: 211560

There's only one way to find out how long it will take, and that's to try it. If you're dealing with a production system you can't take offline you'll need to create a replica from the most recent backup and attempt your migration on that first. If it's on hardware of similar capability and is lightly loaded, like your database will be during the actual migration, then it should give you an idea.

To do a migration like this which locks tables you'll need to take your site offline if this table is critical. Do this prior to your de-duplication efforts.

Usually UNIQUE INDEX is the way to go for these things, as it automatically imposes a constraint as well.

Upvotes: 3

Related Questions