How an index works (B-tree mental model) in SQL - Quick Revision & Summary

Recall & Review

beginner

What is the main purpose of an index in a database?

An index helps the database find data quickly, like a book's index helps you find a topic without reading every page.

intermediate

Describe the structure of a B-tree used in database indexes.

A B-tree is a balanced tree with nodes containing keys and pointers. It keeps data sorted and allows fast searching by narrowing down where to look step-by-step.

beginner

How does a B-tree index speed up searching for a value?

It starts at the top node, compares the value, and moves down the correct branch, skipping large parts of data, so it finds the value quickly without scanning everything.

intermediate

Why is a B-tree called 'balanced'?

Because all leaf nodes are at the same depth, so the time to find any value is similar, preventing slow searches.

advanced

What happens when a B-tree node becomes too full?

The node splits into two nodes and pushes a key up to the parent, keeping the tree balanced and efficient.

What does a database index primarily do?

A Stores all data in memory

B Speeds up data retrieval

C Deletes duplicate data

D Encrypts the database

An index speeds up data retrieval by allowing quick lookups.

In a B-tree index, what is stored in the nodes?

A Keys and pointers to child nodes

B Only data rows

C User passwords

D Database logs

Nodes store keys and pointers to child nodes to guide the search.

Why is a B-tree index called balanced?

A It has the same number of keys in every node

B It uses equal memory for all nodes

C All leaf nodes are at the same depth

D It stores data in alphabetical order

Balanced means all leaf nodes are at the same depth, ensuring consistent search times.

What happens when a B-tree node is full?

A It deletes the oldest key

B It stops accepting new data

C It merges with another node

D It splits and pushes a key up

A full node splits and pushes a key up to keep the tree balanced.

How does a B-tree index improve search speed?

A By narrowing search to branches step-by-step

B By scanning every row quickly

C By storing data in random order

D By compressing data

It narrows the search path step-by-step, skipping irrelevant data.

Explain how a B-tree index helps a database find data quickly.

Think about how a phone book helps you find a name without reading every page.

Describe what happens inside a B-tree when a node becomes too full.

Imagine a bookshelf that gets too full and you add a new shelf to keep things organized.