... continued

This lesson continues the discussion on indexes.

We have learned that pages form the leaf nodes of the tree stored on the disk. Now we’ll see how rows are stored within each page.

Page

The pages of the B-tree split and merge as required. If a page is full and a new key is inserted, the existing page splits. Similarly, if enough rows are deleted from a page it may get merged with another. There are other intricate details around how pages are managed, but they aren’t important for this introductory course on MySQL. Within a leaf-node page, the records or rows exist as a singly linked-list. The linked list enforces the index order on the rows. A new row is added to the available free space within the page. The existing records in the page aren’t moved around in case the new row appears between existing rows in index order. Instead the new row is placed in the free space available within the page and the linked list pointers are manipulated to fix the order. This is depicted below:

Level up your interview prep. Join Educative to access 70+ hands-on prep courses.