0

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.

Improve this question
1
  • With 137 million rows I would be careful using a MD5 hash because you'll probably have collisions.
    – jor
    Commented Feb 24, 2017 at 10:14

1 Answer 1

Reset to default
0

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.

Improve this answer
2
  • 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.
    – acnghm
    Commented Jan 20, 2015 at 14:43
  • Andy, did you suggest pt-online-schema-change? If the index can be added with this tool you wont lock the table.
    – eroomydna
    Commented Jan 20, 2015 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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