Composite index and column order in SQL - Mini Project: Build & Apply

Creating and Using Composite Indexes in SQL

📖 Scenario: You are managing a database for an online bookstore. The books table stores information about each book, including its author, genre, and publication_year. To speed up searches that filter by author and genre, you want to create a composite index.

🎯 Goal: Build a composite index on the books table using the columns author and genre in the correct order to optimize queries filtering by these columns.

📋 What You'll Learn

Create a books table with columns id, author, genre, and publication_year.

Add a composite index named idx_author_genre on the author and genre columns.

Write a query that selects all books by a specific author and genre using the composite index.

Explain the importance of column order in the composite index.

💡 Why This Matters

🌍 Real World

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

💼 Career

Database administrators and developers use composite indexes to optimize query performance and ensure applications run efficiently.

1

Create the books table

Write a SQL statement to create a table called books with columns: id as an integer primary key, author as a VARCHAR(100), genre as a VARCHAR(50), and publication_year as an integer.

SQL

-- Create the books table with the specified columns
-- Your code here

Need a hint?

Use CREATE TABLE with the specified column names and types.

2

Add a composite index on author and genre

Write a SQL statement to create a composite index named idx_author_genre on the books table using the columns author and genre in that order.

SQL

CREATE TABLE books (
    id INT PRIMARY KEY,
    author VARCHAR(100),
    genre VARCHAR(50),
    publication_year INT
);
-- Create composite index idx_author_genre on author and genre
-- Your code here

Need a hint?

Use CREATE INDEX with the index name and specify the columns in the correct order.

3

Write a query using the composite index

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

SQL

CREATE TABLE books (
    id INT PRIMARY KEY,
    author VARCHAR(100),
    genre VARCHAR(50),
    publication_year INT
);
CREATE INDEX idx_author_genre ON books (author, genre);
-- Write a SELECT query filtering by author and genre
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE author = 'Jane Austen' AND genre = 'Romance'.

4

Explain the importance of column order in composite indexes

Add a SQL comment explaining why the order of columns in the composite index idx_author_genre matters for query performance.

SQL

CREATE TABLE books (
    id INT PRIMARY KEY,
    author VARCHAR(100),
    genre VARCHAR(50),
    publication_year INT
);
CREATE INDEX idx_author_genre ON books (author, genre);
SELECT * FROM books WHERE author = 'Jane Austen' AND genre = 'Romance';
-- Add a comment explaining column order importance
-- Your code here

Need a hint?

Write a comment starting with -- explaining that the first column in the index is used first for filtering.