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 thePRMARY 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
SELECT
.If you have better answer, please add a comment about this, thank you!
Source: Stackoverflow.com