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.

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      
  • 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 Rajagopal Sep 19 at 10:43
  • 1
    @ArunRajagopal You forget to specify your server version. It seems it is too ancient. – Akina Sep 19 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 Rajagopal Sep 19 at 10:46
  • 1
    @ArunRajagopal Sorry, I don't know how to do it on your server version. – Akina Sep 19 at 10:56
  • (And if you have a FULLTEXT index, there will be another column.) – Rick James Oct 3 at 23:49
-3

Any Unique Key you create has no need of a PRIMARY key.

Over 8.5 years ago, I answered the post Why do primary keys have names of their own?. I described how a Primary Key is really no different from a Unique Key because all Unique Keys (properly known in Database Theory as Candidate Keys) define a level of uniqueness for each row in a table.

For example, an employee table could have an Employee ID number and a Social Security Number. Each value unique identifies an employee. What makes a Primary Key different from a Unique Key is strictly a matter of choice via application use. The Primary Key is just the default Candidate key to reference and identify table rows. Secondary Indexes will go by PRIMARY Key to determine each row it retrieves.

Unique Keys could also dictate the strictness of data being entered into a table. Suppose you wanted to make sure three columns in a table were unique to a table row. Once you entered the data into the table, would you always look up rows in your table with three columns (from the Unique Key) or just one column (the Primary Key) ? Naturally, using the fewest columns make sense.

With regard to your table, you could have easily designed it as

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 (`b`),
  UNIQUE KEY `a_UNIQUE` (`a`),
  KEY `single` (`c`),
  KEY `double` (`d`,`e`),
  KEY `triple` (`f`,`g`,`h`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

and all SELECT queries would mechanically look up data the same way within the Query Optimizer.

WORD OF CAUTION: Avoid multiple column Primary Keys as much as possible because bigger Primary Keys will bloat all Secondary Keys. Please read MySQL Docs on Clustered Indexes for more information.

  • "Any Unique Key you create has no need of a PRIMARY key." - False (for InnoDB). When looking up the row via the unique key, it needs a pointer to the row. That is the columns(s) of the PK. – Rick James Oct 3 at 23:40
  • "Unique Keys could also dictate ..." -- This is where the PK columns are irrelevant. – Rick James Oct 3 at 23:43
  • "look up data the same way" -- Yes, the "same way", but with b instead of a as the clustered PK. – Rick James Oct 3 at 23:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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