MySQL performance of applying unique constraint single column vs combination column

indexMySQLperformanceunique-constraint

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.

Best Answer

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.

Related Solutions

MySQL looking up more rows than needed (indexing issue)

Your indexes are fine for the two types of queries you mentioned.

This query will be satisfied by traversing the clustered index on the primary key...

[...] WHERE participant_id = x AND question_id = y AND given_answer_id = z;

...and this one is satisfied by the index on 'question_id':

[...] WHERE question_id = x;

The output of EXPLAIN SELECT is not telling you what you think it is telling you, because the value shown in rows is an estimate of the number of rows the server will need to consider, not the actual rows it will examine. For InnoDB these are based on index statistics.

rows

The rows column indicates the number of rows MySQL believes it must examine to execute the query.

For InnoDB tables, this number is an estimate, and may not always be exact.

^{— http://dev.mysql.com/doc/refman/5.5/en/explain-output.html#explain_rows}

The optimizer gathers information about different possible query plans, and chooses the one with the lowest cost. The information shown in EXPLAIN is the information the optimizer gathered about the plan it selected.

When type is ref and key is not NULL, this means that the name listed in the key column is the name of the index that the optimizer has chosen to use to find the desired rows, so your query plan looks exactly as it should.

Note, sometimes you will see Using index in the Extra column and a lot of people assume that this means an index is being used, or that no index is being used when that doesn't appear, but that's not correct, either. Using index describes a special case called a "covering index" -- it does not indicate whether an index is being used to locate the rows of interest.

It's possible that running ANALYZE [LOCAL] TABLE would cause the numbers in rows shown by EXPLAIN to differ, but this is a simple query and selecting this index is an obvious choice for the optimizer to make, so ANALYZE TABLE is unlikely to make any actual difference in performance.

It is possible, however, that your overall performance might see some marginal improvement with an occasional OPTIMIZE [LOCAL] TABLE, because you are not inserting rows in primary key order (as would be the case with an auto_increment primary key)... but on large tables this can be time-consuming because it rebuilds a new copy of the table... but, again, I wouldn't expect any significant change.

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

Other Info

Best Answer

Related Solutions

MySQL looking up more rows than needed (indexing issue)

Mysql – Adding index to large thesql tables

Related Question