1

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?

choxsword
  • 3,187
  • 18
  • 44
  • Questions about the design of third-party applications isn't really on-topic here. SO is for questions about your own programming. – Barmar Mar 30 '23 at 02:59
  • @Barmar where should I ask such question? – choxsword Mar 30 '23 at 03:13
  • 3
    I think the brief answer is that if you update the row, it may move to another page in the tablespace. That would require updating the row pointers in potentially many secondary indexes, which multiplies how many writes are required a lot. Whereas if the secondary indexes link to the rows by PK _value_ instead of by row location, they still work after you update the row. – Bill Karwin Mar 30 '23 at 03:13
  • I don't know, maybe quora.com? – Barmar Mar 30 '23 at 03:17
  • Ugh -- Quora demands payment. – Rick James Apr 01 '23 at 00:55

1 Answers1

1

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222