I have a large MySQL InnoDB table containing around 137 million rows.
I need to add a unique constraint to the table. We have two options:
1) A combination index on three columns, one of which will be new and initially entirely NULL. The column definitions in combination the index would be:
`identifier` int(6) unsigned NOT NULL,
`value` varchar(255) NOT NULL,
`is_unique` tinyint(1) DEFAULT NULL, -- this is the new column
2) A unique index on a new column containing a unique md5 hash (calculated in the code) The definition would be:
`unique_key` CHAR(32) DEFAULT NULL
It would be easier for us to take option one as the code would be simpler, however I am wondering if there is a difference in how long it would take to apply the index in both cases. Does it take significantly longer to apply a combination unique index to a table (one column completely NULL) vs applying a unique index to a single column (completely NULL)? Or is there no difference? I know that MySQL will consider NULL values in unique indexes to not be the same (we need that behavior). If there is a big difference in the time that will affect the solution we go for.
There are currently five indexes (including one combination index) on the table and it has four foreign key constraints to other tables.
MySQL version 5.1.69-enterprise-commercial-advanced-log.