If you look at this documentation for show index in version 8.0, it says

   {FROM | IN} tbl_name
   [{FROM | IN} db_name]
   [WHERE expr]

The optional EXTENDED keyword causes the output to include information about hidden indexes that MySQL uses internally and are not accessible by users.

Without the optional EXTENDED keyword, only the normal index parts are visible, the primary key at the end is not visible (innodb). I want to see the extended version in 5.7. How do I see it?

I tried checking in information_schema tables like TABLE_CONSTRAINTS, KEY_COLUMN_USAGE etc. The extended info is also not there.

  • I am pretty sure the info is not available before 8.0. – Rick James Oct 4 '19 at 17:52
  • But it’s safe to assume PK is indeed present ? – Arun S R Oct 4 '19 at 17:54

But it’s safe to assume PK is indeed present ?

Yes. (The following applies only to InnoDB; other engines probably differ.)

  • A copy of the PK column(s) is tacked on the end of regular indexes.
  • FULLTEXT and SPATIAL work differently, so this discussion may not apply.
  • There has been some chatter about whether the FK columns are redundantly added, or only if not present in the declared index.
  • The benefit of the PK columns being tacked onto an index is for "covering". This provides a (perhaps) 2x speedup. Example: Given PRIMARY KEY(id), if you also have INDEX(x), then SELECT id FROM t WHERE x=1 (without any other columns mentioned), then the query can be performed in the BTree for the index since it is effectively (x,id).
  • UNIQUE and non-UNIQUE work the same except for the uniqueness constraint. This constraint applies only to the columns specified. That is, given PRIMARY KEY(id), if you also have UNIQUE(uuid), then uuid is checked for uniqueness, even though the BTree containing that index has (uuid, id).
  • The PRIMARY KEY is a UNIQUE index, but it is different because it is clustered with the data. That is it is in the same BTree and that BTree is ordered according to the PK.
  • Hence a "point query" using the PK, is a simple drill-down of the PK+data's BTree.
  • But a "point query" using a secondary index needs to first drill down the index's BTree to find the PK, then drill down the PK to find the row. (Exception: "Covering index", see above, avoids this second drill-down.)
|improve this answer|||||

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.