Index selection strategy in MySQL - Mini Project: Build & Apply

Index Selection Strategy in MySQL

📖 Scenario: You are managing a small online bookstore database. You want to improve the speed of searching for books by their title and author.

🎯 Goal: Build a simple table for books and add indexes to optimize search queries on title and author.

📋 What You'll Learn

Create a table called books with columns id, title, and author

Add an index on the title column

Add an index on the author column

Write a query to select books by a specific title using the index

Write a query to select books by a specific author using the index

💡 Why This Matters

🌍 Real World

Indexing is used in real databases to speed up searches, like finding books quickly in an online bookstore.

💼 Career

Database administrators and developers use index selection strategies to optimize query performance and improve user experience.

1

Create the books table

Create a table called books with columns id as an integer primary key, title as VARCHAR(100), and author as VARCHAR(100).

MySQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE books and define the columns with their types and primary key.

2

Add indexes on title and author

Add an index named idx_title on the title column and an index named idx_author on the author column of the books table.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);
-- Your code here to add indexes

Need a hint?

Use CREATE INDEX index_name ON books(column_name) to add indexes.

3

Write a query to find books by title

Write a SELECT query to find all columns from books where the title is exactly 'The Great Gatsby'. Use the title column in the WHERE clause to utilize the index.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);

CREATE INDEX idx_title ON books(title);
CREATE INDEX idx_author ON books(author);

-- Your code here to select books by title

Need a hint?

Use SELECT * FROM books WHERE title = 'The Great Gatsby' to find books by title.

4

Write a query to find books by author

Write a SELECT query to find all columns from books where the author is exactly 'Jane Austen'. Use the author column in the WHERE clause to utilize the index.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);

CREATE INDEX idx_title ON books(title);
CREATE INDEX idx_author ON books(author);

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

-- Your code here to select books by author

Need a hint?

Use SELECT * FROM books WHERE author = 'Jane Austen' to find books by author.