mysqlinnodbnon-clustered-index

Why doesn't innodb store row pointer in secondary/non-clustered index?


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?


Solution

  • 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

    PRIMARY KEY(id),
    INDEX(foo)   -- effectively (foo, id), as discussed above
    
    SELECT id FROM tbl WHERE foo = 123;
    

    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.