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

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

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

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

Hint

Use CREATE INDEX with the exact index name and column.

Practice

1. Which factor most improves the performance of a JOIN operation in MySQL?

easy

A. Having indexes on the columns used in the JOIN condition

B. Using SELECT * to retrieve all columns

C. Joining tables without any WHERE clause

D. Using subqueries instead of JOINs

Solution

1. Step 1: Understand JOIN performance basics

   JOINs work faster when MySQL can quickly find matching rows using indexes on the join columns.

2. Step 2: Evaluate options for performance impact

   Using SELECT * or no WHERE clause does not improve speed; subqueries often perform worse than JOINs.

3. Final Answer:

   Having indexes on the columns used in the JOIN condition -> Option A

4. Quick Check:

   Indexes speed up JOINs = B [OK]

Hint: Indexes on join columns speed up JOINs [OK]

Common Mistakes:

• Thinking SELECT * is faster
• Ignoring the need for indexes
• Assuming subqueries are always faster

2. Which of the following is the correct syntax to perform an INNER JOIN between tables orders and customers on customer_id?

easy

A. SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;

B. SELECT * FROM orders INNER JOIN customers WHERE orders.customer_id = customers.customer_id;

C. SELECT * FROM orders JOIN customers USING (customer_id);

D. SELECT * FROM orders, customers ON orders.customer_id = customers.customer_id;

Solution

1. Step 1: Review INNER JOIN syntax

   The correct INNER JOIN syntax uses JOIN ... ON condition, like in SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;.

2. Step 2: Check other options for syntax errors

   SELECT * FROM orders INNER JOIN customers WHERE orders.customer_id = customers.customer_id; uses WHERE instead of ON, which is invalid for JOIN syntax. SELECT * FROM orders JOIN customers USING (customer_id); requires parentheses around the column name. SELECT * FROM orders, customers ON orders.customer_id = customers.customer_id; mixes comma join with ON, which is invalid.

3. Final Answer:

   SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id; -> Option A

4. Quick Check:

   JOIN ... ON is correct syntax = A [OK]

Hint: Use JOIN ... ON for correct INNER JOIN syntax [OK]

Common Mistakes:

• Using WHERE instead of ON for JOIN condition
• Mixing comma joins with ON clause
• Incorrect use of USING without parentheses

3. Given the tables:
employees(id, name, dept_id)
departments(id, dept_name)
and the query:

SELECT e.name, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.id WHERE d.dept_name = 'Sales';

What will be the result if there are 3 employees in 'Sales' department?

medium

A. Empty result because of missing GROUP BY

B. All employees regardless of department

C. Only department names without employee names

D. 3 rows with employee names and 'Sales' as dept_name

Solution

1. Step 1: Analyze the JOIN and WHERE clause

   The JOIN matches employees to departments by dept_id. The WHERE filters to only 'Sales' department.

2. Step 2: Determine output rows

   Since 3 employees belong to 'Sales', the query returns 3 rows with their names and 'Sales' as dept_name.

3. Final Answer:

   3 rows with employee names and 'Sales' as dept_name -> Option D

4. Quick Check:

   JOIN + WHERE filters = 3 matching rows [OK]

Hint: WHERE filters rows after JOIN, so only matching rows show [OK]

Common Mistakes:

• Assuming all employees are returned
• Expecting aggregation without GROUP BY
• Thinking only department names are returned

4. You wrote this query but it runs very slowly:

SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.country = 'USA';

What is the most likely cause of poor performance?

medium

A. JOIN condition uses wrong columns

B. Using SELECT * instead of specific columns

C. Missing index on customers.country column

D. WHERE clause should be in the JOIN condition

Solution

1. Step 1: Identify performance bottleneck

   The WHERE clause filters by c.country = 'USA'. Without an index on country, MySQL scans all customers.

2. Step 2: Evaluate other options

   SELECT * does not cause slow JOINs. JOIN condition is correct. WHERE clause placement is valid.

3. Final Answer:

   Missing index on customers.country column -> Option C

4. Quick Check:

   Index missing on filter column = C [OK]

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

Common Mistakes:

• Blaming SELECT * for slow performance
• Misplacing WHERE clause inside JOIN
• Assuming JOIN condition is incorrect

5. You have two large tables: products (1 million rows) and sales (10 million rows). You want to find total sales per product. Which approach improves JOIN performance the most?

hard

A. Use a subquery to get sales totals, then join with products without indexes

B. Create indexes on sales.product_id and products.id, then use INNER JOIN with GROUP BY

C. Join without indexes and filter results in application code

D. Use CROSS JOIN and filter with WHERE clause

Solution

1. Step 1: Understand data size and join keys

   Large tables require indexes on join keys to avoid full scans and speed up matching.

2. Step 2: Evaluate join strategies

   INNER JOIN with proper indexes and GROUP BY efficiently aggregates sales per product. Subqueries without indexes or CROSS JOIN cause slow performance.

3. Final Answer:

   Create indexes on sales.product_id and products.id, then use INNER JOIN with GROUP BY -> Option B

4. Quick Check:

   Indexes + JOIN + GROUP BY = best performance [OK]

Hint: Index join keys and aggregate in SQL, not app [OK]

Common Mistakes:

• Skipping indexes on join columns
• Using CROSS JOIN causing huge intermediate results
• Filtering in application instead of SQL