Examples of MySQL EXPLAIN
Examples 1: Total Orders by Customer
Consider a scenario where we have a table named orders with the columns order_id, customer_id, order_date and total_amount. We want to the retrieve the total amount of the orders placed by the each customer.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10, 2)
);
Sample Data:
INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES
(1, 1, '2024-04-01', 100.00),
(2, 1, '2024-04-05', 150.00),
(3, 2, '2024-04-10', 200.00),
(4, 2, '2024-04-15', 120.00);
EXPLAIN SELECT customer_id, SUM(total_amount) AS total_orders
FROM orders
GROUP BY customer_id;
Output:
+----+-------------+--------+-------+--------------+------+---------+------+-----+-------+
| id | select_type | table | type | possible_keys| key | key_len | ref | rows| Extra |
+----+-------------+--------+-------+--------------+------+---------+------+-----+-------+
| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 4 | | Using where; Using filesort
+----+-------------+--------+-------+--------------+------+---------+------+-----+-------+
Explanation:
- The output reveals that the query performs the simple SELECT operation on the orders table.
- It utilizes an index for the GROUP BY operation resulting in the efficient query execution.
Example 2: Product Listing by Category and Price
Let’s consider another scenario where we have a table named products with the columns product_id, category_id, name and price. We want to the retrieve all products from the specific category ordered by the price in the descending order.
Create the products table
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
category_id INT,
name VARCHAR(255),
price DECIMAL(10, 2)
);
Insert sample data into the products table
INSERT INTO products (category_id, name, price) VALUES
(1, 'Product 1', 10.99),
(1, 'Product 2', 20.49),
(2, 'Product 3', 15.99),
(1, 'Product 4', 8.50),
(3, 'Product 5', 30.00);
we can execute the EXPLAIN statement with the query:
EXPLAIN SELECT *
FROM products
WHERE category_id = 1
ORDER BY price DESC;
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | products | ALL | NULL | NULL | NULL | NULL | 5 | Using where; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
Explanation:
- The output indicates a simple SELECT operation on the products table.
- It utilizes an index on category_id column for the efficient data retrieval.
- The ORDER BY clause requires a filesort operation due to the descending order by the price.
How to Read MySQL EXPLAIN?
Understanding how to interpret and analyze the output of the MySQL EXPLAIN statement is essential for database administrators, developers, and anyone involved in optimizing database performance.
The EXPLAIN statement provides insights into how MySQL executes queries and helps identify potential bottlenecks or inefficiencies in the query execution plans. By mastering the interpretation of the EXPLAIN output users can optimize queries, improve database performance, and enhance overall application efficiency.