Is this second multicolumn unique key in my MySQL statement redundant or does it improve performance-CodePudding

I found this old code and I'm not sure if it's optimized or just doing something silly.

I have a SQL create statement like this:

CREATE TABLE `wp_pmpro_memberships_categories` (
  `membership_id` int(11) unsigned NOT NULL,
  `category_id` int(11) unsigned NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY `membership_category` (`membership_id`,`category_id`),
  UNIQUE KEY `category_membership` (`category_id`,`membership_id`)
);

Is that second UNIQUE KEY there redundant with the PRIMARY KEY on the same 2 columns? Or would the second one help for queries that filter by the category_id first then by the membership_id? Is it being ignored?

I'm trying to remember why I coded it that way, way back when. Seems similar to what this comment is describing: https://dba.stackexchange.com/a/1793/245678

Thanks!

CodePudding user response：

It depends on your query patterns. If you do SELECT, UPDATE, DELETE only on the category_id column, then the 2nd index makes sense but you should omit the membership_id column (redundant) and the UNIQUE constraint.

MySQL will automatically use the PRIMARY KEY index if you use either membership_id or both columns. It doesn't matter in which order these columns appear in your WHERE clauses.

CodePudding user response：

The secondary index does improve performance when going from a "category" to a "membership".
You coded it with those two indexes because some queries start with a "membership" and need to locate a "category"; some queries go the 'other' direction.
That's a well-coded "many-to-many mapping table".
InnoDB provides better performance than MyISAM.
The "Uniqueness" constraint in the UNIQUE key is redundant.
Checking for Uniqueness slows dowing writes by a very small amount. (The constraint must be checked before finishing the update to the index's BTree. A non-unique index can put off the update until later; see "change buffering".)
I like to say this to indicate that I have some reason for the pair of columns being together in the index:
```
INDEX(`category_id`,`membership_id`)
```
I discuss the schema pattern here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table