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?
Best Answer
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:
This makes your unique index less wide than the primary key index, which is why it is chosen by the optimizer.