How do indexes work internally (e.g., B-tree indexes)?

B-tree index is a balanced tree that allows for fast searching, inserting, and deleting records by key.

Working principle:

• The tree's root node contains keys and references to child nodes.
• Each node contains an ordered set of keys and pointers to subtrees or data.
• Search begins at the root, comparing the sought key with the keys in the node to determine which branch to continue the search.
• Due to its balanced nature, the tree's depth is minimal, ensuring logarithmic search time.

B-tree is well-suited for indexing columns with a large number of unique values and supports range queries.

Example: when searching for a record with key 50, the algorithm compares 50 with the keys in the root, selects the appropriate child node, and repeats the process until it finds the desired leaf with data or determines that the key is absent.