I have beed reading quite a lot about he InnoDB engine internals and its locking mechanism (different locks and types of deadlocks)

I've seen this error when doing update operation:

'Deadlock found when trying to get lock; try restarting transaction' 

when performing update operation and followed what is advised here to create an index on field called resource_id.

I wonder if there is a difference/ added value to avoid deadlocks in MySql DB, by adding new Index on a column that is already defined as PrimaryKey.

Why MySql does not block you to add Index on the same column that is already primary key? this leads me to the understanding that there is a difference.

CodePudding user response：

There has been some talk about disallowing any truly redundant indexes.

It is, however, debatable whether PRIMARY KEY(x), INDEX(x) is really redundant. For example, if you have a query that only needs x, then the INDEX would be preferable to the seeming equivalent PK.

There is a big difference. The PK is "clustered" with the data. That is, the PK is not stored separately, but, instead, the data is stored in PK order. This obviates the need for a separate BTree for just the PK.

Except for the rare example I gave above.

On the other hand, I can think of no justification for INDEX(x), INDEX(x) except for failure to catch it as redundant. Note that in creating a FOREIGN KEY, which does create an INDEX, there is a real attempt to avoid creating another index.

Note that INDEX(x) should in virtually all cases be removed if you also have INDEX(x,y) That does not apply with UNIQUE(x), INDEX(x,y). And, since the PK is effectively a UNIQUE index, there is an obvious correlary.

Shall I go on? There may be a dozen more subtle points that are somewhat related to your question.

I think the developers see your request as "low priority". There's an old saying "If it ain't broke, don't fix it." For MySQL, I will paraphrase it as "If it ain't broke much, don't bother fixing it; find something more important to work on."