CODEWITHSUNDEEP
mysqlruby-on-railsrubyrails-migrations
Aparichith
Aparichith

Reputation: 1535

Multiple index on same column

I have a table which already have a column with BTREE index on it. Now I want to add a unique key constraint to the same column to avoid race condition from my rails app.

All the reference blogs/article shows I have to add a migration to create a new uniq index on that column like below

add_index :products, :key, :string, unique: true

I want to understand

Upvotes: 0

Views: 636

Answers (2)

Akina
Akina

Reputation: 42834

What happens to BTREE index which is already present?(I need this)

Nothing. Creating a new index does not affect existing indexes.

Is it OK to have both the index and they both work fine?

Two indices by the same expression which differs in uniqueness only? This makes no sense.

It is recommended to remove regular index when unique one is created. This will save a lot of disk space. Additionally - when regular and unique indices by the same expression (literally!) exists then server will never use regular index.

Table has around 30MN entries, will it locks the table while adding index and take huge time to add this UNIQUE index?

The table will be locked shortly at the start of the index creation process. But if index creation and parallel CUD operations are executed then both of them will be slower.

The time needed for index creation can be determined only in practice. Sometimes it cannot be even predicted.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562911

You don't need both indexes.

In MySQL's default storage engine InnoDB, a UNIQUE KEY index is also a BTREE. InnoDB only supports BTREE indexes, whether they are unique or not (it also supports fulltext indexes, but that's a different story).

So a unique index is also useful for searching and sorting, just like a non-unique index.

Building an index will lock the table. I suggest using an online schema change tool like pt-online-schema-change or gh-ost. We use the former at my company, and we run hundreds of schema changes per week on production tables without blocking access. In fact, using one of these tools might cause the change to take longer, but we don't care because we aren't suffering any limited access while it's running.

Upvotes: 1

Related Questions