Composite indexes in MySQL - Mini Project: Build & Apply

Creating and Using Composite Indexes in MySQL

📖 Scenario: You are managing a small online bookstore database. You want to speed up searches that filter books by both author and publication year.

🎯 Goal: Create a table called books with columns for id, title, author, and year. Then add a composite index on author and year to improve search speed.

📋 What You'll Learn

Create a table named books with columns id (integer primary key), title (varchar 100), author (varchar 50), and year (integer).

Add a composite index named idx_author_year on the author and year columns.

Write a query that uses the composite index to find all books by author 'Jane Austen' published in 1813.

Ensure the final table and index creation statements are correct and the query uses the index.

💡 Why This Matters

🌍 Real World

Composite indexes are used in real databases to speed up searches that filter on multiple columns, such as finding books by author and year.

💼 Career

Database administrators and backend developers often create composite indexes to optimize query performance in applications.

1

Create the books table

Write a SQL statement to create a table called books with these columns: id as an integer primary key, title as varchar(100), author as varchar(50), and year as integer.

MySQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE books and define each column with its type. Make id the primary key.

2

Add a composite index on author and year

Write a SQL statement to add a composite index named idx_author_year on the author and year columns of the books table.

MySQL

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

Need a hint?

Use CREATE INDEX idx_author_year ON books (author, year); to create the composite index.

3

Write a query using the composite index

Write a SQL query to select all columns from books where author is 'Jane Austen' and year is 1813. Use the composite index you created.

MySQL

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

CREATE INDEX idx_author_year ON books (author, year);
-- Your code here to query books by Jane Austen published in 1813

Need a hint?

Use SELECT * FROM books WHERE author = 'Jane Austen' AND year = 1813; to use the composite index.

4

Complete the database setup with the composite index

Ensure the full SQL script includes the CREATE TABLE statement, the CREATE INDEX idx_author_year ON books (author, year); statement, and the query selecting books by 'Jane Austen' published in 1813.

MySQL

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

CREATE INDEX idx_author_year ON books (author, year);

SELECT * FROM books WHERE author = 'Jane Austen' AND year = 1813;
-- Your code here if needed to complete

Need a hint?

Make sure all parts are included in the final script.