JOIN performance considerations in MySQL - Mini Project: Build & Apply

JOIN performance considerations

📖 Scenario: You are managing a small online bookstore database. You want to understand how to efficiently join tables to get book details along with their authors without slowing down your queries.

🎯 Goal: Build a simple database setup with two tables and write a JOIN query that retrieves book titles with their author names, considering performance best practices.

📋 What You'll Learn

Create two tables: books and authors with specified columns

Insert sample data into both tables

Write a JOIN query to get book titles and author names

Add an index to improve JOIN performance

💡 Why This Matters

🌍 Real World

Online stores and many applications use JOINs to combine related data from multiple tables efficiently.

💼 Career

Understanding JOIN performance and indexing is essential for database administrators and backend developers to optimize data retrieval.

1

Create tables authors and books

Write SQL statements to create a table called authors with columns author_id (integer primary key) and author_name (varchar 100). Also create a table called books with columns book_id (integer primary key), title (varchar 100), and author_id (integer foreign key).

MySQL

-- Create the authors table
-- Create the books table

Need a hint?

Use CREATE TABLE statements with the exact column names and types.

2

Insert sample data into authors and books

Insert these exact rows into authors: (1, 'Jane Austen'), (2, 'Mark Twain'). Insert these exact rows into books: (101, 'Pride and Prejudice', 1), (102, 'Adventures of Huckleberry Finn', 2).

MySQL

CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(100)
);

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author_id INT
);

-- Insert data into authors
-- Insert data into books

Need a hint?

Use INSERT INTO with the exact values given.

3

Write a JOIN query to get book titles with author names

Write a SELECT query that joins books and authors on author_id to get columns title and author_name.

MySQL

CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(100)
);

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author_id INT
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain');

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Adventures of Huckleberry Finn', 2);

-- Write a SELECT query joining books and authors

Need a hint?

Use JOIN with ON to connect the tables by author_id.

4

Add an index on books.author_id to improve JOIN performance

Write an SQL statement to create an index named idx_author_id on the author_id column of the books table.

MySQL

CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(100)
);

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author_id INT
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain');

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Adventures of Huckleberry Finn', 2);

SELECT books.title, authors.author_name
FROM books
JOIN authors ON books.author_id = authors.author_id;

-- Create an index on books.author_id

Need a hint?

Use CREATE INDEX with the exact index name and column.