Why indexes speed up queries in MySQL - See It in Action

Why Indexes Speed Up Queries

📖 Scenario: You are managing a small online bookstore database. You want to understand how adding an index can make searching for books faster.

🎯 Goal: Build a simple table of books, add an index on the title column, and write a query that uses the index to quickly find a book by its title.

📋 What You'll Learn

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

Insert 5 specific book titles into the books table.

Add an index on the title column.

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

💡 Why This Matters

🌍 Real World

Indexes are used in real databases to make searching large amounts of data fast and efficient, like finding a book by title in an online bookstore.

💼 Career

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

1

Create the books table and insert data

Create a table called books with columns id as an integer primary key and title as text. Then insert these 5 books exactly: (1, 'The Great Gatsby'), (2, 'Moby Dick'), (3, 'Hamlet'), (4, 'War and Peace'), (5, 'Pride and Prejudice').

MySQL

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

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO to add the books.

2

Add an index on the title column

Add an index named idx_title on the title column of the books table to speed up searches by title.

MySQL

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

INSERT INTO books (id, title) VALUES
  (1, 'The Great Gatsby'),
  (2, 'Moby Dick'),
  (3, 'Hamlet'),
  (4, 'War and Peace'),
  (5, 'Pride and Prejudice');

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

Need a hint?

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

3

Write a query to find the book titled 'The Great Gatsby'

Write a SELECT query to get all columns from books where the title is exactly 'The Great Gatsby'.

MySQL

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

INSERT INTO books (id, title) VALUES
  (1, 'The Great Gatsby'),
  (2, 'Moby Dick'),
  (3, 'Hamlet'),
  (4, 'War and Peace'),
  (5, 'Pride and Prejudice');

CREATE INDEX idx_title ON books(title);

-- Write a SELECT query to find the book 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 how the index speeds up the query

Add a comment explaining that the index on title helps the database find the book faster by avoiding a full table scan.

MySQL

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

INSERT INTO books (id, title) VALUES
  (1, 'The Great Gatsby'),
  (2, 'Moby Dick'),
  (3, 'Hamlet'),
  (4, 'War and Peace'),
  (5, 'Pride and Prejudice');

CREATE INDEX idx_title ON books(title);

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

-- Add a comment explaining how the index speeds up the query
-- Your code here

Need a hint?

Write a comment starting with -- that says the index helps avoid scanning the whole table.