Query optimization techniques in MySQL - Mini Project: Build & Apply

Query Optimization Techniques in MySQL

📖 Scenario: You are managing a small online bookstore database. The database has a table called books that stores information about each book, including its id, title, author, genre, and price. You want to learn how to write efficient queries to quickly find books based on their genre and price.

🎯 Goal: Build a simple MySQL query that selects books from the books table filtered by genre and price, then optimize the query by adding an index to improve performance.

📋 What You'll Learn

Create a books table with specified columns

Insert sample data into the books table

Write a SELECT query filtering books by genre and price

Add an index on the genre and price columns to optimize the query

💡 Why This Matters

🌍 Real World

Optimizing database queries is essential for fast websites and applications that handle lots of data.

💼 Career

Database administrators and backend developers use query optimization to improve system speed and user experience.

1

Create the books table and insert sample data

Create a table called books with columns id (integer primary key), title (varchar 100), author (varchar 100), genre (varchar 50), and price (decimal 5,2). Then insert these exact rows: (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Classic', 10.99), (2, '1984', 'George Orwell', 'Dystopian', 8.99), (3, 'To Kill a Mockingbird', 'Harper Lee', 'Classic', 7.99), (4, 'The Hobbit', 'J.R.R. Tolkien', 'Fantasy', 12.50), (5, 'Brave New World', 'Aldous Huxley', 'Dystopian', 9.50).

MySQL

-- Create the books table and insert sample data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Set filter values for genre and price

Create two variables called filter_genre and max_price and set them to 'Dystopian' and 10.00 respectively. These will be used to filter the books.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100),
  genre VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, author, genre, price) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Classic', 10.99),
(2, '1984', 'George Orwell', 'Dystopian', 8.99),
(3, 'To Kill a Mockingbird', 'Harper Lee', 'Classic', 7.99),
(4, 'The Hobbit', 'J.R.R. Tolkien', 'Fantasy', 12.50),
(5, 'Brave New World', 'Aldous Huxley', 'Dystopian', 9.50);

-- Set filter variables
SET @filter_genre = 'Dystopian';
SET @max_price = 10.00;  -- Your code here

Need a hint?

Use SET @variable_name = value; to create variables in MySQL.

3

Write a SELECT query filtering by genre and price

Write a SELECT query that retrieves all columns from books where genre equals @filter_genre and price is less than or equal to @max_price.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100),
  genre VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, author, genre, price) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Classic', 10.99),
(2, '1984', 'George Orwell', 'Dystopian', 8.99),
(3, 'To Kill a Mockingbird', 'Harper Lee', 'Classic', 7.99),
(4, 'The Hobbit', 'J.R.R. Tolkien', 'Fantasy', 12.50),
(5, 'Brave New World', 'Aldous Huxley', 'Dystopian', 9.50);

SET @filter_genre = 'Dystopian';
SET @max_price = 10.00;

SELECT * FROM books WHERE genre = @filter_genre AND price <= @max_price;  -- Your code here

Need a hint?

Use SELECT * FROM books WHERE genre = @filter_genre AND price <= @max_price;

4

Add an index on genre and price to optimize the query

Add a composite index on the genre and price columns of the books table to help MySQL run the query faster.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100),
  genre VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, author, genre, price) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Classic', 10.99),
(2, '1984', 'George Orwell', 'Dystopian', 8.99),
(3, 'To Kill a Mockingbird', 'Harper Lee', 'Classic', 7.99),
(4, 'The Hobbit', 'J.R.R. Tolkien', 'Fantasy', 12.50),
(5, 'Brave New World', 'Aldous Huxley', 'Dystopian', 9.50);

SET @filter_genre = 'Dystopian';
SET @max_price = 10.00;

SELECT * FROM books WHERE genre = @filter_genre AND price <= @max_price;

-- Add index below
-- Your code here

Need a hint?

Use CREATE INDEX idx_genre_price ON books (genre, price); to add the index.