Mysql – Why is Primary Key not present at the end of Unique Key in MySQL

indexMySQLmysql-5.7optimizationunique-constraint

I have a table with following definition.

CREATE TABLE `test` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `e` int(11) DEFAULT NULL,
  `f` int(11) DEFAULT NULL,
  `g` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `h` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `b_UNIQUE` (`b`),
  KEY `single` (`c`),
  KEY `double` (`d`,`e`),
  KEY `triple` (`f`,`g`,`h`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

For some queries when I check optimizer trace I notice key parts for indices.

For example:

{
  "index": "single",
  "usable": true,
  "key_parts": [
    "c",
    "a"
  ]
},
{
  "index": "double",
  "usable": true,
  "key_parts": [
    "d",
    "e",
    "a"
  ]
}

Notice for indices single and double the key part contains the Primary Key a at the end. That is what InnoDB says. So thats good. 

{
  "index": "b_UNIQUE",
  "usable": true,
  "key_parts": [
    "b"
  ]
}

But as seen in above snippet, the b_Unique key does not contain primary key a as the final key part. Is it really not present and pointer to record is present? What implications does this have with respect to query optimization and Page reorganisation?

EDIT 1: It seems to be present in MySQL 8.0 version. It is highly likely to be present in MySQL version 5.7 as well. So require a way to check its presence in this version.

Best Answer

But as seen in above snippet, the b_Unique key does not contain primary key a as the final key part.

It is a snippet problem.

show index from test

Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression
:---- | ---------: | :------- | -----------: | :---------- | :-------- | ----------: | -------: | :----- | :--- | :--------- | :------ | :------------ | :------ | :---------
test  |          0 | PRIMARY  |            1 | a           | A         |           0 |     null | null   |      | BTREE      |         |               | YES     | null      
test  |          0 | b_UNIQUE |            1 | b           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          1 | single   |            1 | c           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          1 | double   |            1 | d           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          1 | double   |            2 | e           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          1 | triple   |            1 | f           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          1 | triple   |            2 | g           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          1 | triple   |            3 | h           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null      

show extended index from test

Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression
:---- | ---------: | :------- | -----------: | :---------- | :-------- | ----------: | -------: | :----- | :--- | :--------- | :------ | :------------ | :------ | :---------
test  |          0 | PRIMARY  |            1 | a           | A         |           0 |     null | null   |      | BTREE      |         |               | YES     | null      
test  |          0 | PRIMARY  |            2 | DB_TRX_ID   | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null      
test  |          0 | PRIMARY  |            3 | DB_ROLL_PTR | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null      
test  |          0 | PRIMARY  |            4 | b           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          0 | PRIMARY  |            5 | c           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          0 | PRIMARY  |            6 | d           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          0 | PRIMARY  |            7 | e           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          0 | PRIMARY  |            8 | f           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          0 | PRIMARY  |            9 | g           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          0 | PRIMARY  |           10 | h           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          0 | b_UNIQUE |            1 | b           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          0 | b_UNIQUE |            2 | a           | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null      
test  |          1 | single   |            1 | c           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          1 | single   |            2 | a           | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null      
test  |          1 | double   |            1 | d           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          1 | double   |            2 | e           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          1 | double   |            3 | a           | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null      
test  |          1 | triple   |            1 | f           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          1 | triple   |            2 | g           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          1 | triple   |            3 | h           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null      
test  |          1 | triple   |            4 | a           | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null
Related Question