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 KEYserve 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
If you have better answer, please add a comment about this, thank you!