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).

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

Procedures in PL/SQL

Procedures in PL/SQL (Procedural Language/Structured Query Language) are a way to encapsulate a set of SQL and PL/SQL statements that perform a specific task. They are stored in the database and can be executed as needed, providing a modular and reusable approach to database programming. Here’s an overview of creating and using procedures in PL/SQL: Creating a Procedure To create a procedure, you use the CREATE PROCEDURE statement followed by the procedure's name, parameters (if any), and the body of the procedure, which contains the executable code. Here’s a basic syntax: CREATE [OR REPLACE] PROCEDURE procedure_name     [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ] IS    -- Declaration of variables BEGIN    -- Executable statements EXCEPTION    -- Exception handling END procedure_name; Example Here’s a simple example of a PL/SQL procedure that inserts a new employee record into an employees table: CREATE OR REPLACE PROCEDURE add_employee (     p_emp_id IN employees.emp
Read more

Cursors in PL/SQL

In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit Cursors PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements. Example of Implicit Cursor DECLARE     v_employee_name employees.first_name%TYPE; BEGIN     -- This SELECT INTO statement uses an implicit cursor     SELECT first_name     INTO v_employee_name     FROM employees     WHERE employee_id = 100;     DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable. Explicit Cursors Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result s
Read more