MySQL performance of applying unique constraint single column vs combination column

Question

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.

Other Info

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.

• jor: With 137 million rows I would be careful using a MD5 hash because you'll probably have collisions.

Answer 1

The duration of the creation of the index will be directly related to the size of the index. I don't think it would be wildly different for the 2 options provided. Under the hood MySQL is going to copy the table to recreate it in the new structure, so consider the amount of time MySQL will take to read the data and write the data. The process is single threaded too. If your storage is slow this will take some time.

Tips: - Ensure that fast_index_creation is enabled - Group multiple ALTERs - use pt-online-schema-change tool to perform it online. This will take longer but will permit you to continue to use the table.

• acnghm: Thanks. Turns out the DBAs won't index the table as it's too big (85gb) so would lock for hours. However, your comments are helpful and informative.
• eroomydna: Andy, did you suggest pt-online-schema-change? If the index can be added with this tool you wont lock the table.