CODEWITHSUNDEEP
mysqlindexing
M Charles
M Charles

Reputation: 175

Am I duplicating a MySQL index or is there a value in having both?

I have a table with several fields that I'm trying to index:

user_id = Int that divides the data between user accounts.
item_number = Non-unique Int that a user sets when creating new items.

I have two indexes:

idx_user_id: Fields = user_id, Type = BTREE, Unique = NO
idx_user_id_item_number: Fields = user_id AND item_number, Type =
BTREE, Unique = YES

I'm wondering if idx_user_id is unnecessary because we also start our idx_user_id_item_number with user_id and queries will use that and get the same result. My only concern is that because the idx_user_id_item_number index is unique, it might be necessary to keep both.

Upvotes: 1

Views: 27

Answers (2)

Rick James
Rick James

Reputation: 142238

  • Having a PRIMARY KEY is important.
  • A PK is UNIQUE.

So, get rid of both indexes and have only

PRIMARY KEY(user_id, item_number)

Upvotes: 0

O. Jones
O. Jones

Reputation: 108641

Your index on (user_id) is redundant with your index on (user_id, item_number). You don't need the first index. You should drop it, because it takes storage space and slows down update and insert operations.

For the purpose of indexing MySQL uses the leading column or columns in a multicolumn index as if they were a standalone index.

Notice this though, if your index on (user_id) were a unique index and the other one was not, you'd need both indexes. Why? in that case you'd be using the first index for the purpose of enforcing the uniqueness constraint.

Upvotes: 1

Related Questions