The table schema for example:

  `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`)

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`)
  • 1
    You already have it: UNIQUE KEY uqx_bar1_bar2 (bar1,bar2). Even KEY idx_bar1 (bar1) is unnecessary. – mustaccio Mar 12 '18 at 16:48

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

| improve this answer | |

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).

| improve this answer | |
  • Not quite right -- (bar1, bar2) does not help if you need (bar2); only leftmost works. – Rick James Apr 2 '18 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.