MySQL InnoDB: Primary Key always included in secondary indexes as the right-most columns… or not

Recently during the Swedish MySQL User Group (SMUG), I presented a session dedicated to MySQL InnoDB Primary Keys. I forgot to mention a detail that many people are not aware, but Jeremy Cole has pointed out. Primary Key always included in secondary indexes at the right-most column When we define a secondary index, the secondary index includes the Primary Key as the right-most column of the index. It’s silently added, meaning that it’s not visible but it’s used to point back to the record in the clustered index. This is an example with a table having a Primary Key composed of multiple columns: CREATE TABLE `t1` ( `a` int NOT NULL, `b` int NOT NULL, `c` int NOT NULL, `d` int NOT NULL, `e` int NOT NULL, `f` varchar(10) DEFAULT 'aaa', `inserted` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(`a`,`b`,`c`) ) ENGINE=InnoDB; And this is the table’s content (only 2 records): SELECT * FROM t1; +---+---+---+----+----+-----+---------------------+ | a | b | c | d | e | f | inserted | +---+---+---+----+----+-----+---------------------+ | 1 | 2 | 3 | 4 | 5 | abc | 2024-02-11 17:37:16 | | 7 | 8 | 9 | 10 | 11 | def | 2024-02-11 17:37:26 | +---+---+---+----+----+-----+---------------------+ Now let’s create a secondary key for the column f: ALTER TABLE t1 ADD INDEX f_idx(f); This key will then include the Primary Key as the right-most column(s) on the secondary index: The orange filled entries are the hidden one. Let’s verify this on the InnoDB page for that index: And indeed, we can see that the Primary Key columns (in red) are included in each entry of the secondary index (in purple). But not always ! When we have a Primary Key or Part of a Primary Key included in a Secondary Index, only the eventual missing columns of the Primary Key index will be added as right-most and hidden entries to the secondary index. Let’s create a secondary index where the column b will be missing: ALTER TABLE t1 ADD INDEX sec_idx (`d`,`c`,`e`,`a`); The column b will be indeed added as the right-most hidden column of the index. Let’s verify this: We can see above, that indeed, the value of column b is added. Same for the second record: If we check in the InnoDB source code, this is also commented: But what will happen, if we just use a prefix part of the Primary Key in the secondary index? Let’s try: CREATE TABLE `t1` ( `a` varchar(10) NOT NULL DEFAULT 'aaaaaaaaaa', `b` varchar(10) NOT NULL DEFAULT 'bbbbbbbbbb', `c` int NOT NULL DEFAULT '1', `f` varchar(10) DEFAULT NULL, PRIMARY KEY (`a`,`b`,`c`), KEY `sec_idx` (`c`,`f`,`a`(2)) ) ENGINE=InnoDB SELECT * FROM t1; +------------+------------+---+-----+ | a | b | c | f | +------------+------------+---+-----+ | aaaaaaaaaa | bbbbbbbbbb | 1 | abc | | ccccccccc | dddddddddd | 2 | def | +------------+------------+---+-----+ We can see that only 2 characters of the column a are used in the secondary index. If we check in the InnoDB page, we can notice that in-fact, the full column will also be added as the right-most hidden part of the secondary index: So InnoDB needs to have the full PK, visible or hidden in the secondary index. This is something not often known. But let’s hope this blog post clarifies it. Enjoy MySQL & InnoDB… and of course, don’t forget to use Primary Keys !