When indexes help and when they hurt in SQL - Mini Project: Build & Apply

Understanding When Indexes Help and When They Hurt

📖 Scenario: You are managing a small online bookstore database. You want to learn how adding indexes can speed up searching for books by title but also understand when indexes might slow down data changes like adding new books.

🎯 Goal: Build a simple books table, add an index on the title column, run a query using the index, then see how inserting a new book is affected by the index.

📋 What You'll Learn

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

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

Write a query to select all books where the title contains the word 'SQL'.

Insert a new book into the books table and observe the presence of the index.

💡 Why This Matters

🌍 Real World

Indexes are used in real databases to speed up searching for data, like finding books by title quickly in an online store.

💼 Career

Database administrators and developers use indexes to optimize query performance and understand trade-offs when inserting or updating data.

1

Create the books table

Create a table called books with two columns: id as an integer primary key and title as text.

SQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE with id INTEGER PRIMARY KEY and title TEXT.

2

Add an index on the title column

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

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT
);
-- Your code here to create the index

Need a hint?

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

3

Query books using the index

Write a query to select all columns from books where the title contains the word 'SQL'. Use the LIKE operator with '%SQL%'.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT
);

CREATE INDEX idx_title ON books(title);

-- Your code here to select books with 'SQL' in the title

Need a hint?

Use SELECT * FROM books WHERE title LIKE '%SQL%'; to find books with 'SQL' in the title.

4

Insert a new book and understand index impact

Insert a new book with id 1 and title 'Learning SQL Basics' into the books table. This shows how indexes exist while adding data.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT
);

CREATE INDEX idx_title ON books(title);

SELECT * FROM books WHERE title LIKE '%SQL%';

-- Your code here to insert a new book

Need a hint?

Use INSERT INTO books (id, title) VALUES (1, 'Learning SQL Basics'); to add the book.