1

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.

Mike Baranczak
  • 8,291
  • 8
  • 47
  • 71

2 Answers2

2

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.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • Will the table be locked for reading and writing, or just writing? – Mike Baranczak Jun 27 '17 at 17:46
  • 1
    During schema alterations the whole table is locked, period, you can't read or write to it. All queries attempting access to it will backlog until it's complete. – tadman Jun 27 '17 at 17:47
  • Are you sure? What about this: https://dev.mysql.com/doc/refman/5.7/en/alter-table.html#alter-table-concurrency – Mike Baranczak Jun 27 '17 at 17:49
  • As far as I know InnoDB's MVCC doesn't support reading during schema alterations. Other engines might. I know Postgres has much better support for this, but that's an entirely different database. – tadman Jun 27 '17 at 17:51
  • 1
    Your information on locking is incorrect, see: https://stackoverflow.com/a/35426301/365719 . Since 5.6, MySQL allows altering a table without locking. Before that, it only required a write lock. – Mike Baranczak Jun 27 '17 at 21:56
  • Ah, that's a new feature then. If you can confirm this behaviour it's worth writing an answer that includes it. I've been using MariaDB more, which is a MySQL fork, and it seems to lag behind on features like this. – tadman Jun 27 '17 at 22:13
0

use ghost migration if you have replica

H4F
  • 91
  • 8