JOIN performance considerations in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use indexes with JOINs?

When joining two large tables in MySQL, why is it important to have indexes on the join columns?

A Indexes automatically merge the tables before the join happens.

B Indexes speed up the search for matching rows, reducing the time to find join matches.

C Indexes prevent duplicate rows from appearing in the join result.

D Indexes compress the data to use less disk space during the join.

💡 Hint

Think about how the database finds matching rows quickly.

Indexes help the database quickly locate matching rows in join columns, making the join operation faster.

❓ query_result

intermediate

Result count of INNER JOIN with filtering

Given two tables, employees and departments, with 1000 and 10 rows respectively, and an INNER JOIN on department_id, what is the number of rows returned if only 5 departments have employees?

MySQL

SELECT e.employee_id, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id WHERE d.department_id IN (1,2,3,4,5);

A The number of employees in those 5 departments.

B Always 1000 rows because all employees are joined.

C 10 rows because there are 10 departments.

D 5 rows because only 5 departments are filtered.

💡 Hint

Think about how INNER JOIN and WHERE filter the rows.

The query returns all employees who belong to the 5 specified departments, so the count equals the number of employees in those departments.

📝 Syntax

advanced

Identify the correct JOIN syntax for performance optimization

Which of the following JOIN queries is syntactically correct and optimized for performance by using explicit JOIN syntax?

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

B SELECT * FROM orders LEFT JOIN customers USING (customer_id);

C SELECT * FROM orders, customers WHERE orders.customer_id = customers.id;

D SELECT * FROM orders JOIN customers WHERE orders.customer_id = customers.id;

💡 Hint

Look for the correct JOIN keyword and ON clause.

Option A uses explicit INNER JOIN with ON clause, which is clear and optimized for performance. Option A uses old implicit join syntax. Option A uses USING with a wrong column name. Option A misses ON clause.

❓ optimization

advanced

Best practice to reduce JOIN query time on large tables

You have two large tables joined on a column with no index. Which action will most improve the JOIN query performance?

A Increase the server's RAM without changing the query.

B Rewrite the query to use UNION instead of JOIN.

C Use SELECT * to fetch all columns for faster retrieval.

D Add an index on the join column in both tables.

💡 Hint

Think about how the database finds matching rows efficiently.

Adding indexes on join columns allows the database to quickly find matching rows, greatly improving join performance.

🔧 Debug

expert

Diagnose the cause of slow JOIN query

A JOIN query between two large tables runs very slowly. The query is:

SELECT a.id, b.name FROM table_a a JOIN table_b b ON a.ref_id = b.id WHERE b.status = 'active';

Which of the following is the most likely cause of the slow performance?

A The join condition uses <code>a.ref_id = b.id</code> which is invalid syntax.

B The WHERE clause filters on <code>b.status</code> but <code>b.id</code> is not indexed.

C Missing index on <code>b.status</code> column causing full scan after join.

D Using JOIN instead of LEFT JOIN causes slower execution.

💡 Hint

Consider which columns are filtered and joined and their indexes.

Without an index on b.status, the database must scan all rows after the join, slowing the query. Indexing b.status helps filter rows early.