EXPLAIN query analysis in MySQL - Mini Project: Build & Apply

Understanding EXPLAIN Query Analysis in MySQL

📖 Scenario: You are working with a small online bookstore database. You want to understand how MySQL runs your queries to make them faster and more efficient.

🎯 Goal: Learn how to use the EXPLAIN statement to analyze a SQL query and understand its execution plan.

📋 What You'll Learn

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

Insert some sample data into the books table.

Write a SELECT query to find books by a specific author.

Use the EXPLAIN statement to analyze the SELECT query.

💡 Why This Matters

🌍 Real World

Database developers and administrators use EXPLAIN to understand and improve query performance in real applications.

💼 Career

Knowing how to analyze query execution plans is essential for optimizing databases and ensuring fast response times in software jobs.

1

Create the books table

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

MySQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Insert sample data into books

Insert three rows into the books table with these exact values: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, '1984', 'George Orwell'), and (3, 'To Kill a Mockingbird', 'Harper Lee').

MySQL

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

Need a hint?

Use a single INSERT INTO statement with multiple rows.

3

Write a SELECT query to find books by 'George Orwell'

Write a SQL SELECT statement to get all columns from books where the author is exactly 'George Orwell'.

MySQL

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

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, '1984', 'George Orwell'),
(3, 'To Kill a Mockingbird', 'Harper Lee');

-- Your code here to select books by George Orwell

Need a hint?

Use SELECT * FROM books WHERE author = 'George Orwell'.

4

Use EXPLAIN to analyze the SELECT query

Write a SQL statement that uses EXPLAIN before the SELECT query to see how MySQL executes it. Use the exact query: EXPLAIN SELECT * FROM books WHERE author = 'George Orwell';

MySQL

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

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, '1984', 'George Orwell'),
(3, 'To Kill a Mockingbird', 'Harper Lee');

SELECT * FROM books WHERE author = 'George Orwell';

-- Your code here to add EXPLAIN before the SELECT query

Need a hint?

Simply add EXPLAIN before the SELECT query.