Query Analysis using Query Plan

A query plan (or execution plan) is a detailed, step-by-step map that a database management system (DBMS) like MySQL follows to execute a SQL query. It describes the operations the DBMS will perform to retrieve the required data. Understanding query plans is essential for optimizing query performance.

Query Plan

A query plan outlines how the DBMS will access data and which methods it will use to perform joins, filters, sorts, and other operations. The plan includes information about:The order in which tables are accessed.

• The indexes that are used.
• The type of joins performed.
• The filtering and sorting mechanisms.
• The estimated cost of various operations.

Understanding the Execution Plan with EXPLAIN

Basic Usage of EXPLAIN

To analyze a query, prepend EXPLAIN to your SQL statement. This will show you how MySQL plans to execute the query.sql

EXPLAIN SELECT * FROM employees WHERE employee_id = 123;

Optimizing with Query Plans

By analyzing the query plan, you can identify potential bottlenecks and take steps to optimize the query:

• Indexing: Ensure that indexes are present on columns used in WHERE clauses, JOIN conditions, and ORDER BY clauses.
• Query Refactoring: Rewrite queries to make them more efficient. For example, use joins instead of subqueries.
• Limiting Result Set: Use LIMIT to reduce the number of rows processed.
• Partitioning: Partition large tables to improve query performance.

Example:

CREATE TABLE employees (

    employee_id INT PRIMARY KEY,

    first_name VARCHAR(50),

    last_name VARCHAR(50),

    department_id INT,

    salary DECIMAL(10, 2)

);

CREATE TABLE departments (

    department_id INT PRIMARY KEY,

    department_name VARCHAR(50)

);

INSERT INTO departments (department_id, department_name) VALUES (1, 'Human Resources');

INSERT INTO departments (department_id, department_name) VALUES (2, 'Finance');

INSERT INTO departments (department_id, department_name) VALUES (3, 'IT');

INSERT INTO departments (department_id, department_name) VALUES (4, 'Marketing');

INSERT INTO departments (department_id, department_name) VALUES (5, 'Sales');

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (101, 'John', 'Doe', 1, 55000);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (102, 'Jane', 'Smith', 2, 60000);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (103, 'Mike', 'Brown', 3, 75000);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (104, 'Emily', 'Davis', 4, 48000);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (105, 'David', 'Wilson', 5, 67000);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (106, 'Linda', 'Johnson', 1, 52000);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (107, 'Chris', 'Lee', 3, 80000);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (108, 'Anna', 'Taylor', 2, 62000);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (109, 'Tom', 'Walker', 4, 45000);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (110, 'Sophia', 'White', 5, 71000);

EXPLAIN SELECT e.first_name, e.last_name, d.department_name

FROM employees e

JOIN departments d ON e.department_id = d.department_id

WHERE e.salary > 50000;

Output:

+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                                    | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------------------------+------+----------+-------------+
|  1 | SIMPLE      | e     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                                   |   10 |    33.33 | Using where |
|  1 | SIMPLE      | d     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | sandbox_db.e.department_id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------------------------+------+----------+-------------+

Columns Explanation

• id: The sequence number of the SELECT within the query.
• select_type: The type of SELECT (e.g., SIMPLE, PRIMARY, UNION).
• table: The table to which the row of the output refers.
• type: The join type (e.g., ALL, index, range, ref, eq_ref).ALL: Full table scan.
• index: Full index scan.
• range: Index scan for a range of values.
• ref: Non-unique index lookup.
• eq_ref: Unique index lookup.
• possible_keys: The possible indexes MySQL could use to find the rows in this table.
• key: The actual key that MySQL decided to use.
• key_len: The length of the key used.
• ref: The columns or constants compared to the index.
• rows: The number of rows MySQL estimates it needs to read to find the right rows.
• Extra: Additional information (e.g., Using index, Using where).