Is it benefit to create a index key if unique key already exists on the same columns?

Question

The table schema for example:

CREATE TABLE `foos` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bar1` bigint(20) unsigned NOT NULL,
  `bar2` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uqx_bar1_bar2` (`bar1`,`bar2`),
  KEY `idx_bar1` (`bar1`),
  KEY `idx_bar2` (`bar2`),
  CONSTRAINT `fk_bar1` FOREIGN KEY (`bar1`) REFERENCES `bars1` (`id`),
  CONSTRAINT `fk_bar2` FOREIGN KEY (`bar2`) REFERENCES `bars2` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

This table is created for m:n relations, index key is created separately for bar1 and bar2, and unique key is created for both columns bar1 and bar2.

In most cases, this query will be executed:

SELECT * FROM foos WHERE bar1=? AND bar2=?

The question is: Will query optimizer get benefit if index key created for both columns bar1 and bar2? That is:

ALTER TABLE foos ADD INDEX `idx_bar1_bar2` (`bar`,`bar2`)

Answer 1

More discussion on building indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Furthermore your table smells like a many:many mapping table?? Here are several tips on efficiency for such: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Answer 2

There is already using primary key i.e, clustered Index and then used UNIQUE KEY on uqx_bar1_bar2 (bar1,bar2). So there is no more needed KEY idx_bar1 (bar1), KEY idx_bar2 (bar2).