What's a B-Tree index structure?

A B-Tree (Balanced Tree) is the most common data structure used for indexes in databases like MySQL (specifically in the InnoDB engine).

To visualize it, think of an upside-down tree with its "root" at the top and "leaves" at the bottom.

How it looks

1. The Root Node: The starting point. It contains "guideposts" that tell MySQL which direction to go.
2. Internal (Branch) Nodes: These are middle layers that further narrow down the search.
3. Leaf Nodes: These are at the very bottom. They contain the actual data (or a pointer to the data) and are linked together horizontally.

How it works (The Search Process)

Imagine you are looking for ID 25 in a large index:

1. Root: The root might say: "Left for IDs 1-50, Right for IDs 51-100." You go Left.
2. Branch: The next level says: "Left for 1-25, Right for 26-50." You go Left.
3. Leaf: You land exactly on the node containing 25.

The "Magic" of B-Trees: Even if you have 1 million rows, a B-Tree is so efficient that it usually only takes 3 or 4 levels to find any specific record. Searching 4 levels is much faster than checking 1,000,000 rows!

Key Characteristics

• Sorted Order: The data in a B-Tree is always kept in sorted order. This is why ORDER BY and range searches (like price BETWEEN 10 AND 50) are so fast.
• Perfectly Balanced: The "B" stands for Balanced. The tree automatically reorganizes itself as you add or delete data so that the distance from the top to any leaf is always roughly the same. This ensures consistent performance.
• Dual-Directional: Leaf nodes are linked to their neighbors (left and right). This allows MySQL to read through a range of data very quickly once it finds the starting point.

Why use B-Tree for MySQL?

• Efficiency: It minimizes "Disk I/O" (reading from the hard drive). Because the tree is "short and fat" (many branches, few levels), the database only needs to read a few blocks of data to find what it needs.
• Flexibility: It works great for exact matches (=), ranges (>, <), and sorting (ORDER BY).

In your current LabEx environment, when you created that products table, the PRIMARY KEY (id) automatically created a B-Tree index. So whenever you search by id, MySQL is performing this lightning-fast tree navigation!

Do you want to see how to check if an index is being used for a query?