In this step, you will learn how to compare the performance of different types of subqueries in MySQL. Understanding the performance characteristics of subqueries is crucial for writing efficient SQL queries, especially when dealing with large datasets.
-
Connect to the MySQL Server:
If you are not already connected, connect to your MySQL server using the mysql client:
sudo mysql -u root
-
Select the Database:
Select the labdb database:
USE labdb;
-
Add More Data to the orders Table:
To make the performance comparison more realistic, let's add a significant amount of data to the orders table. We'll insert 1000 orders for each customer using a stored procedure. Execute the following SQL commands in the MySQL prompt:
DELIMITER //
CREATE PROCEDURE insert_many_orders()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 1000 DO
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1, CURDATE(), 50.00);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (2, CURDATE(), 75.00);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (3, CURDATE(), 100.00);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (4, CURDATE(), 125.00);
SET i = i + 1;
END WHILE;
END//
DELIMITER ;
CALL insert_many_orders();
DROP PROCEDURE insert_many_orders;
Explanation:
- This SQL script creates a stored procedure called
insert_many_orders.
- The procedure inserts 1000 orders for each of the four customers into the
orders table.
- After inserting the data, the procedure is dropped.
-
Query using IN:
Execute the following query using IN to find all customers who have placed at least one order:
SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
-
Analyze the Query Execution Plan using EXPLAIN:
Before executing the query, use the EXPLAIN command to analyze the query execution plan. This will give you insights into how MySQL plans to execute the query and identify potential performance bottlenecks. Execute the following SQL command in the MySQL prompt:
EXPLAIN SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
The output of EXPLAIN will show you the tables being accessed, the indexes being used (if any), and the order in which the operations are performed. Pay attention to the type column, which indicates the type of join or access method used.
-
Query using EXISTS:
Execute the following query using EXISTS to find all customers who have placed at least one order:
SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
-
Analyze the Query Execution Plan using EXPLAIN:
Use the EXPLAIN command to analyze the query execution plan for the EXISTS query. Execute the following SQL command in the MySQL prompt:
EXPLAIN SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
Compare the execution plan with the one from the IN query. Are there any differences in the tables accessed, indexes used, or access methods?
-
Observations:
In general, EXISTS tends to perform better than IN when the subquery returns a large number of rows. This is because IN needs to compare the outer query's values against all the values returned by the subquery, while EXISTS stops as soon as it finds a match. However, the actual performance can vary depending on the specific query, the data, and the database system. You can use the BENCHMARK() function (as shown in the original document) to get a more precise measurement of execution time, but for this lab, analyzing the EXPLAIN output provides sufficient insight into the query plans.
-
Clean up (Optional):
If you want to clean up the database and tables, you can execute the following commands in the MySQL prompt:
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP DATABASE IF EXISTS labdb;
Remember to exit the MySQL client by typing exit and pressing Enter.
