How an index works (B-tree mental model) in SQL - Practice Exercises

Challenge - 5 Problems

🧠 Conceptual

intermediate

How does a B-tree index speed up data search?

Imagine a phone book organized as a B-tree index. What is the main reason this structure helps find a name faster than scanning every page?

A It randomly picks entries to check, hoping to find the name quickly.

B It stores all data in one big list, so the computer reads everything faster.

C It divides the data into smaller groups, allowing quick jumps to the right section instead of checking every entry.

D It duplicates the entire data multiple times to speed up reading.

💡 Hint

Think about how you find a word in a dictionary without reading every page.

A B-tree index splits data into smaller parts, letting the search jump directly to the relevant section, avoiding a full scan.

❓ query_result

intermediate

What rows does this indexed query return?

Given a table Employees with a B-tree index on LastName, what rows will this query return?

SELECT * FROM Employees WHERE LastName = 'Smith';

SQL

CREATE TABLE Employees (ID INT, FirstName VARCHAR(50), LastName VARCHAR(50));
CREATE INDEX idx_lastname ON Employees(LastName);
-- Assume data includes multiple 'Smith' entries

A Only the first row where LastName is 'Smith', ignoring others.

B All rows where LastName is exactly 'Smith', found quickly using the index.

C All rows, because the index is ignored in equality searches.

D No rows, because indexes do not affect query results.

💡 Hint

Indexes help find matching rows faster but do not change which rows are returned.

The index helps the database quickly locate all rows with LastName 'Smith', returning all matching rows.

📝 Syntax

advanced

Which SQL statement correctly creates a B-tree index?

Choose the correct SQL command to create a B-tree index on the email column of the Users table.

A CREATE INDEX idx_email USING BTREE ON Users(email);

B CREATE BTREE INDEX idx_email ON Users(email);

C CREATE INDEX idx_email ON Users USING BTREE(email);

D CREATE INDEX idx_email ON Users(email);

💡 Hint

Standard SQL uses a simple syntax for creating indexes; specifying B-tree is often default.

Option D is the standard syntax. Options A, B, and D are invalid or non-standard in most SQL databases.

❓ optimization

advanced

Why might a B-tree index be inefficient for a column with many repeated values?

Consider a B-tree index on a column that has only three distinct values repeated many times. Why might this index not improve query speed much?

A Because the index nodes become large and searching them is slower than scanning the table.

B Because B-tree indexes only work with unique values and ignore duplicates.

C Because the database cannot use indexes on columns with repeated values.

D Because the index will cause the database to scan the entire table anyway.

💡 Hint

Think about how useful an index is when many rows share the same value.

When many rows share the same value, the index points to large groups, making it less efficient than scanning.

🔧 Debug

expert

What error occurs with this incorrect B-tree index creation?

What error will this SQL statement produce?

CREATE INDEX idx_wrong ON Users(email,);

A Syntax error due to trailing comma in column list.

B Runtime error because the index name is invalid.

C No error; index created successfully.

D Error because 'email' column does not exist.

💡 Hint

Check the syntax carefully, especially commas in lists.

The trailing comma after 'email' causes a syntax error in SQL.

Practice

1. What is the main purpose of a B-tree index in a database?

easy

A. To speed up data retrieval by organizing data in a balanced tree structure

B. To store data in a random order for faster insertion

C. To compress data to save disk space

D. To backup the database automatically

Solution

1. Step 1: Understand what a B-tree index does

   A B-tree index organizes data in a balanced tree structure to allow quick searching without scanning the entire table.

2. Step 2: Compare options with B-tree purpose

   Only To speed up data retrieval by organizing data in a balanced tree structure describes speeding up data retrieval using a balanced tree, which matches the B-tree index function.

3. Final Answer:

   To speed up data retrieval by organizing data in a balanced tree structure -> Option A

4. Quick Check:

   B-tree index = fast data lookup [OK]

Hint: B-tree indexes speed up searches by organizing data hierarchically [OK]

Common Mistakes:

• Thinking indexes store data randomly
• Confusing index with data compression
• Believing indexes backup data

2. Which SQL statement correctly creates a B-tree index on the column last_name of the table employees?

easy

A. CREATE INDEX idx_last_name ON employees USING HASH (last_name);

B. CREATE INDEX idx_last_name ON employees (last_name) USING BITMAP;

C. CREATE INDEX idx_last_name ON employees (last_name);

D. CREATE INDEX idx_last_name ON employees USING FULLTEXT (last_name);

Solution

1. Step 1: Identify default index type in SQL

   By default, CREATE INDEX creates a B-tree index unless specified otherwise.

2. Step 2: Check syntax correctness

   CREATE INDEX idx_last_name ON employees (last_name); uses correct syntax without specifying a different index type, so it creates a B-tree index on last_name.

3. Final Answer:

   CREATE INDEX idx_last_name ON employees (last_name); -> Option C

4. Quick Check:

   Default index type = B-tree [OK]

Hint: Default index type is B-tree unless stated otherwise [OK]

Common Mistakes:

• Using HASH or FULLTEXT without need
• Adding unsupported index types like BITMAP in standard SQL
• Incorrect syntax for index creation

3. Given a table products with a B-tree index on product_id, what will the query optimizer most likely do when running:
SELECT * FROM products WHERE product_id = 123;?

medium

A. Use the B-tree index to quickly locate the row with product_id 123

B. Perform a full table scan ignoring the index

C. Use a hash index lookup instead of the B-tree

D. Scan only the first 10 rows of the table

Solution

1. Step 1: Understand query condition and index

   The query searches for a specific product_id value, which matches the indexed column.

2. Step 2: Predict optimizer behavior with B-tree index

   The optimizer uses the B-tree index to quickly find the matching row without scanning the whole table.

3. Final Answer:

   Use the B-tree index to quickly locate the row with product_id 123 -> Option A

4. Quick Check:

   Indexed search = fast lookup [OK]

Hint: Queries on indexed columns use B-tree for fast lookup [OK]

Common Mistakes:

• Assuming full table scan always happens
• Confusing B-tree with hash index usage
• Thinking partial scan happens without index

4. You created a B-tree index on the email column of the users table, but queries filtering by email are still slow. What is a likely cause?

medium

A. The index was created on the wrong column

B. The query uses a function on email preventing index use

C. B-tree indexes do not speed up queries on text columns

D. The table has too few rows for the index to help

Solution

1. Step 1: Analyze query and index interaction

   If the query applies a function (like LOWER(email)) on the indexed column, the B-tree index cannot be used directly.

2. Step 2: Evaluate other options

   The index was created on the wrong column is unlikely if index was created correctly; C is false because B-tree indexes work on text; D is possible but less likely than B.

3. Final Answer:

   The query uses a function on email preventing index use -> Option B

4. Quick Check:

   Functions on indexed columns block B-tree use [OK]

Hint: Functions on indexed columns disable B-tree index use [OK]

Common Mistakes:

• Assuming B-tree indexes don't work on text
• Ignoring query transformations on columns
• Blaming index creation without checking query

5. You have a large table orders with columns order_id, customer_id, and order_date. You often query orders by customer_id and sort by order_date. How should you create an index to optimize these queries?

hard

A. Create a single-column B-tree index on order_date

B. Create separate B-tree indexes on customer_id and order_date

C. Create a hash index on customer_id

D. Create a composite B-tree index on (customer_id, order_date)

Solution

1. Step 1: Understand query pattern

   Queries filter by customer_id and sort by order_date, so both columns matter together.

2. Step 2: Choose index type for filtering and sorting

   A composite B-tree index on (customer_id, order_date) supports filtering by customer and sorting by date efficiently.

3. Final Answer:

   Create a composite B-tree index on (customer_id, order_date) -> Option D

4. Quick Check:

   Composite index matches filter + sort columns [OK]

Hint: Composite indexes speed up queries filtering and sorting on multiple columns [OK]

Common Mistakes:

• Creating index only on sort column
• Using separate indexes instead of composite
• Choosing hash index which doesn't support sorting