Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 7 down vote accepted

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.

share|improve this answer
    
I know it includes the PK, because if it'd need all columns and didn't contain all of them it'd use that key to pull the rest in as well. However, I can't see why the indexer would choose that constraint over the other one. (I guess from performance perspective both are identical until a column would be added.) –  Aidiakapi Mar 27 at 20:19
    
See the last edit. –  ypercube Mar 27 at 20:23
    
Interesting, thanks, I'll accept it once the timer has expired ^^ –  Aidiakapi Mar 27 at 20:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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