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

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

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

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

Hint

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

Practice

1. What is the main purpose of using indexes in MySQL query optimization?

easy

A. To encrypt the data for security

B. To speed up data retrieval by reducing the search area

C. To store backup copies of data

D. To increase the size of the database

Solution

1. Step 1: Understand what indexes do

   Indexes help the database find rows faster by creating a quick lookup structure.

2. Step 2: Relate indexes to query speed

   By reducing the number of rows scanned, indexes speed up data retrieval significantly.

3. Final Answer:

   To speed up data retrieval by reducing the search area -> Option B

4. Quick Check:

   Indexes improve search speed = D [OK]

Hint: Indexes speed up searches by narrowing data to scan [OK]

Common Mistakes:

• Thinking indexes increase database size only
• Confusing indexes with backups
• Believing indexes encrypt data

2. Which of the following is the correct syntax to check the query execution plan in MySQL?

easy

A. EXPLAIN SELECT * FROM users;

B. PLAN SELECT * FROM users;

C. SHOW PLAN SELECT * FROM users;

D. DESCRIBE PLAN SELECT * FROM users;

Solution

1. Step 1: Recall the command to view query plans

   MySQL uses the EXPLAIN keyword before a SELECT query to show the execution plan.

2. Step 2: Identify the correct syntax

   Only EXPLAIN SELECT * FROM users; is valid syntax to check the plan.

3. Final Answer:

   EXPLAIN SELECT * FROM users; -> Option A

4. Quick Check:

   EXPLAIN shows query plan = C [OK]

Hint: Use EXPLAIN before SELECT to see query plan [OK]

Common Mistakes:

• Using SHOW PLAN which is invalid in MySQL
• Forgetting EXPLAIN keyword
• Confusing DESCRIBE with EXPLAIN

3. Given the table employees(id INT PRIMARY KEY, name VARCHAR(50), department_id INT) with an index on department_id, what will be the effect of this query?
SELECT * FROM employees WHERE department_id = 5;

medium

A. The query will scan the entire table without using the index

B. The query will return no rows because indexes filter data

C. The query will fail due to missing index

D. The query will use the index to quickly find rows with department_id = 5

Solution

1. Step 1: Understand index usage on department_id

   Since there is an index on department_id, MySQL can use it to locate matching rows efficiently.

2. Step 2: Analyze query behavior

   The WHERE clause filters by department_id = 5, so the index helps avoid scanning all rows.

3. Final Answer:

   The query will use the index to quickly find rows with department_id = 5 -> Option D

4. Quick Check:

   Index on filter column speeds query = B [OK]

Hint: Indexed columns in WHERE speed up searches [OK]

Common Mistakes:

• Assuming index is ignored automatically
• Thinking query fails without index
• Believing indexes filter out rows

4. You wrote this query:
SELECT * FROM orders WHERE customer_id = 10 AND status = 'shipped';
You notice it runs slowly. Which fix will most likely improve performance?

medium

A. Add ORDER BY customer_id

B. Remove the WHERE clause

C. Add an index on (customer_id, status)

D. Use SELECT COUNT(*) instead of SELECT *

Solution

1. Step 1: Identify slow query cause

   The query filters on two columns but may lack a composite index to speed filtering.

2. Step 2: Apply composite index for optimization

   Adding an index on (customer_id, status) helps MySQL quickly find matching rows using both filters.

3. Final Answer:

   Add an index on (customer_id, status) -> Option C

4. Quick Check:

   Composite index speeds multi-column filters = A [OK]

Hint: Use composite index for multiple WHERE columns [OK]

Common Mistakes:

• Removing WHERE clause loses filtering
• ORDER BY does not speed filtering
• Changing SELECT * to COUNT(*) changes result, not speed

5. You have a large table sales(date DATE, product_id INT, amount DECIMAL) without indexes. You want to find total sales for product 101 in 2023 quickly. Which optimization technique is best?

hard

A. Create an index on (product_id, date) and use a query filtering both

B. Add a full table scan hint to force scanning all rows

C. Use SELECT * without WHERE to get all data

D. Drop the table and recreate it

Solution

1. Step 1: Identify query needs

   The query filters by product_id and date range, so indexing these columns helps speed filtering.

2. Step 2: Apply composite index for efficient filtering

   Creating an index on (product_id, date) allows MySQL to quickly find relevant rows without scanning the whole table.

3. Final Answer:

   Create an index on (product_id, date) and use a query filtering both -> Option A

4. Quick Check:

   Composite index on filter columns = A [OK]

Hint: Index columns used in WHERE for faster queries [OK]

Common Mistakes:

• Forcing full table scan slows query
• Selecting all rows wastes time
• Dropping table loses data