0

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.

0

1 Answer 1

2

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      
5
  • Why am I getting ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'extended index from test' at line 1 when I try to run show extended index from test;?
    – Arun S R
    Sep 19, 2019 at 10:43
  • 1
    @ArunRajagopal You forget to specify your server version. It seems it is too ancient.
    – Akina
    Sep 19, 2019 at 10:44
  • Yes checked the documentation. It is available only from 8.0. I am using 5.7. Is there any other way I can check extended index details in MySQL 5.7? Somewhere in Information Schema?
    – Arun S R
    Sep 19, 2019 at 10:46
  • 1
    @ArunRajagopal Sorry, I don't know how to do it on your server version.
    – Akina
    Sep 19, 2019 at 10:56
  • (And if you have a FULLTEXT index, there will be another column.)
    – Rick James
    Oct 3, 2019 at 23:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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