Why does InnoDB table by default uses the UNIQUE constraint instead of the PRIMARY KEY?

Question

In my MySQL database I have a table with 4 columns, the PRIMRAY KEY, two columns with a FOREIGN KEY CONSTRAINT and one UNIQUE index on all fields except for the PRIMARY KEY.

The SHOW CREATE TABLE:

CREATE TABLE `zdb_userbeschikbaarheid` (
  `UserBeschikbaarheid_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `UB_User_ID` int(10) unsigned NOT NULL,
  `UB_PlanDagdeelTaak_ID` int(10) unsigned NOT NULL,
  `UB_Datum` date NOT NULL,
  PRIMARY KEY (`UserBeschikbaarheid_ID`),
  UNIQUE KEY `UQ_UB_User_ID_PlanDagdeelTaak_ID_Datum` (`UB_Datum`,`UB_User_ID`,`UB_PlanDagdeelTaak_ID`),
  KEY `FK_UB_User_ID` (`UB_User_ID`),
  KEY `FK_UB_PlanDagdeelTaak_ID` (`UB_PlanDagdeelTaak_ID`),
  CONSTRAINT `FK_UB_PlanDagdeelTaak_ID` FOREIGN KEY (`UB_PlanDagdeelTaak_ID`) REFERENCES `zdb_plandagdeeltaak` (`PlanDagdeelTaak_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_UB_User_ID` FOREIGN KEY (`UB_User_ID`) REFERENCES `zdb_user` (`User_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=522 DEFAULT CHARSET=utf8

For some weird reason when I select the full table it's using the UNIQUE index instead of the PRIMARY KEY. An EXPLAIN SELECT * FROM zdb_userbeschikbaarheid; yields:

id  select_type table                   type    possible_keys   
1   SIMPLE      zdb_userbeschikbaarheid index   \N

key                                     key_len ref rows    Extra
UQ_UB_User_ID_PlanDagdeelTaak_ID_Datum  11      \N  415     Using index

It will not use the PRIMARY KEY until I specifically tell it to ORDER BY the primary key.
EXPLAIN SELECT * FROM zdb_userbeschikbaarheid ORDER BY UserBeschikbaarheid_ID;

id  select_type table                   type    possible_keys
1   SIMPLE      zdb_userbeschikbaarheid index   \N

key     key_len ref rows    Extra
PRIMARY 4       \N  415     \N

It just seems odd that the UNIQUE KEY is chosen instead of the PRIMARY KEY.

What's causing this behavior, is it a potential problem for my database? And if possible, how can I change it to use the PRIMARY KEY by default?

Answer 1

With InnoDB tables, all secondary indexes include the columns of the clustered index (which is the primary key), appended in the end. So your unique index has actually 4 columns, the 3 you have defined plus the 1 primary key column.

When running a query that needs a full table scan, both indexes have all the data needed, so the optimizer is free to choose any one of the two indexes. In fact, as explained in InnoDB Table and Index Structures, the clustered index contains some additional information that costs 13 more bytes per row:

Records in the clustered index contain fields for all user-defined columns. In addition, there is a 6-byte transaction ID field and a 7-byte roll pointer field.

This makes your unique index less wide than the primary key index, which is why it is chosen by the optimizer.