CODEWITHSUNDEEP
mysqldatabaseindexing
steavy
steavy

Reputation: 1576

Duplicated index in MySql table

Assume I have a table in MySql database like this:

Pet PetId - [bigint] Name - [varchar(50)]

And PetId column is my Primary key. This means that I have PRIMARY index for PetId column.

And assume I've created another index as UNIQUE for the same PetId column like in screenshoot: enter image description here

Will MySql actually create another index for PetId_Uniq and will there be any performance differences?

I know this is kind of silly to create indexes like that. But I've found such situation in an existing database and I'm curious if this PetId_Uniq will end up as a separate index in MySql(which will be an overhead) or will be just ignored(so it's just about clearing up mess in the schema).

Upvotes: 0

Views: 392

Answers (1)

Janez Kuhar
Janez Kuhar

Reputation: 4286

Both indexes get generated. But 8.3.1 How MySQL Uses Indexes states:

If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows (the most selective index).

Upvotes: 1

Related Questions