Let's say we have a table with the following structure:
CREATE TABLE Persons ( PersonID int NOT NULL PRIMARY KEY, LastName varchar(255), FirstName varchar(255) ); CREATE INDEX Lastname ON Persons (LastName);
Also, you can notice we have a secondary index called
From the documentation of MySql(InnoDB engine), it is clear that the secondary index is referencing the primary key. So my questions are:
- what happens if we update a primary key? How does InnoDB quickly update all secondary index entries? Is there a reference from the Primary key index page to all secondary index pages?
- what happens if we update the field "LastName"? As the secondary index is nonunique, how does MySQL search for the page to be updated in the secondary index? It looks like there should be a reference from PK index to all secondary indexes pages of the given row. But I can not find explicit mention of this in the documentation of MySql.