Why indexes matter in SQL - See It in Action

Why Indexes Matter

📖 Scenario: You are managing a small online bookstore database. The store has a table called books that stores information about each book, including its id, title, and author. As the number of books grows, searching for books by title becomes slower.To improve search speed, you will learn how to create an index on the title column.

🎯 Goal: Create a table called books with columns id, title, and author. Then create an index on the title column to speed up searches.

📋 What You'll Learn

Create a table named books with columns id (integer), title (text), and author (text).

Insert three specific books into the books table.

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

Write a query to select all books with the title 'The Great Gatsby'.

💡 Why This Matters

🌍 Real World

Indexes are used in real databases to make searching large amounts of data faster and more efficient.

💼 Career

Database administrators and developers use indexes to optimize query performance and improve user experience.

1

Create the books table and insert data

Write SQL code to create a table called books with columns id (integer), title (text), and author (text). Then insert these three books exactly: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, '1984', 'George Orwell'), and (3, 'To Kill a Mockingbird', 'Harper Lee').

SQL

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

Need a hint?

Use CREATE TABLE books (id INTEGER, title TEXT, author TEXT); to create the table. Use INSERT INTO books (id, title, author) VALUES (...), (...), (...); to add the three books.

2

Add an index on the title column

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

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);

INSERT INTO books (id, title, author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

-- Create an index on the title column
-- Your code here

Need a hint?

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

3

Write a query to find books by title

Write a SQL query to select all columns from the books table where the title is exactly 'The Great Gatsby'.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);

INSERT INTO books (id, title, author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

CREATE INDEX idx_title ON books(title);

-- Select all books with title 'The Great Gatsby'
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE title = 'The Great Gatsby'; to find the book.

4

Explain why the index helps

Add a SQL comment explaining why creating the index idx_title on the title column helps speed up searches.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);

INSERT INTO books (id, title, author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

CREATE INDEX idx_title ON books(title);

SELECT * FROM books WHERE title = 'The Great Gatsby';

-- Add a comment explaining why the index helps
-- Your code here

Need a hint?

Write a comment starting with -- that says the index helps speed up searches by avoiding full table scans.