How an index works (B-tree mental model) in SQL - Try It Yourself

How an index works (B-tree mental model)

📖 Scenario: You are managing a small library database. You want to understand how indexes help find books faster by organizing data like a tree.

🎯 Goal: Build a simple table of books, create an index on the book titles, and write a query that uses the index to find a book quickly.

📋 What You'll Learn

Create a table called books with columns id (integer) and title (text).

Insert exactly these three rows into books: (1, 'Algorithms'), (2, 'Databases'), (3, 'Networking').

Create an index called idx_title on the title column of books.

Write a query to select all columns from books where title equals 'Databases'.

💡 Why This Matters

🌍 Real World

Indexes are used in real databases to make searching large tables fast, like finding a book by title in a library system.

💼 Career

Database administrators and developers use indexes to optimize queries and improve application performance.

1

Create the books table and insert data

Write SQL to create a table called books with columns id as integer and title as text. Then insert these rows exactly: (1, 'Algorithms'), (2, 'Databases'), and (3, 'Networking').

SQL

-- Create the books table and insert rows
-- Your code here

Hint

Use CREATE TABLE to make the table, then INSERT INTO to add each row.

2

Create an index on the title column

Write SQL to create an index called idx_title on the title column of the books table.

SQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES (1, 'Algorithms');
INSERT INTO books (id, title) VALUES (2, 'Databases');
INSERT INTO books (id, title) VALUES (3, 'Networking');
-- Create the index idx_title on title
-- Your code here

Hint

Use CREATE INDEX idx_title ON books(title); to make the index.

3

Write a query to find the book titled 'Databases'

Write a SQL query to select all columns from books where the title is exactly 'Databases'.

SQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES (1, 'Algorithms');
INSERT INTO books (id, title) VALUES (2, 'Databases');
INSERT INTO books (id, title) VALUES (3, 'Networking');
CREATE INDEX idx_title ON books(title);
-- Write the SELECT query to find the book with title 'Databases'
-- Your code here

Hint

Use SELECT * FROM books WHERE title = 'Databases'; to find the book.

4

Explain how the index helps find the book faster

Add a comment explaining that the index idx_title organizes the title values in a B-tree structure, which helps the database find the book titled 'Databases' quickly without scanning all rows.

SQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES (1, 'Algorithms');
INSERT INTO books (id, title) VALUES (2, 'Databases');
INSERT INTO books (id, title) VALUES (3, 'Networking');
CREATE INDEX idx_title ON books(title);
SELECT * FROM books WHERE title = 'Databases';
-- Add a comment explaining how the index helps
-- Your code here

Hint

Write a comment starting with -- explaining the B-tree helps find titles fast.

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