Resolved: Why doesn’t innodb store row pointer in secondary/non-clustered index?

In this post, we will see how to resolve Why doesn’t innodb store row pointer in secondary/non-clustered index?

Question:

As we know, innodb stores only primary key value in its secondary index, which means we need to traverse the clustered index B+ tree again to fetch the row record.
Why not just store the row pointer in secondary index to reduce the extra finding work?

Best Answer:

There is no “row pointer”. The columns of the PRMARY KEY serve the function of locating the row — in the data’s BTree.
Sure, looking up via the PK is arguably slower than a “row pointer”. But Updates, Deletes, block splits, etc., automatically handled. (Cf Bill’s Comment.) This keeps the code simpler. And, in some situations, faster.
A trivial example of faster: Given
In this example, the index’s BTree has the complete answer; no need to reach into the data’s BTree. Hence, the index is called “covering” for this SELECT.

If you have better answer, please add a comment about this, thank you!

Source: Stackoverflow.com