MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

EXPLAIN
The MySQL EXPLAIN Statement
The MySQL EXPLAIN statement is used to provide the execution plan of a query. This statement works similar to the DESCRIBE query; while the DESCRIBE query provides the structure plan of a table, the EXPLAIN statement describes how a query is being executed.
You can use the EXPLAIN statement in situations where a query is taking too much time in order to be executed. It displays the execution plan of such slower queries, allowing you to apply indexes wherever necessary to speed up the execution process.
Note that you cannot use too many indexes on a query either; as it might make the query even slower.
This statement works with the SELECT, DELETE, INSERT, REPLACE and UPDATE statements.
Syntax
Following is the syntax of the EXPLAIN statement 
EXPLAIN tbl_name [col_name | wild]
Example
Assume we have created a table named CUSTOMERS in MySQL database as shown below 
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   ADDRESS CHAR (25),
   PRIMARY KEY (ID)
);
You can use the EXPLAIN statement to view the execution plan of this table as shown below 
EXPLAIN CUSTOMERS;
The output will provide information about the table's structure, including columns and their attributes as follows 
Field
Type
Null
Key
Default
Extra
ID
int
NO
PRI
NULL
﻿
NAME
varchar(20)
NO
﻿
NULL
﻿
ADDRESS
char(25)
YES
﻿
NULL
﻿
You can also use the EXPLAIN statement to obtain details about a specific column as shown below 
EXPLAIN CUSTOMERS NAME;
Output
Following is the output obtained 
Field
Type
Null
Key
Default
Extra
NAME
varchar(20)
NO
﻿
NULL
﻿
EXPLAIN is most commonly used with SELECT queries to analyze their execution plans. Consider the following query 
EXPLAIN SELECT * FROM CUSTOMERS WHERE NAME LIKE 'k%';
The table obtained is as follows 
id
select_type
table
partitions
type
possible_keys
1
SIMPLE
CUSTOMERS
NULL
ALL
NULL
Note that not all columns in the table have been displayed in the output above; there are additional columns present.
EXPLAIN and ANALYZE
If we use the EXPLAIN statement with ANALYZE, it gives additional information such as timing of the execution and iterator-based information like 
Estimated execution cost.
Estimated number of returned rows.
Time to return first row.
Time to return all rows (actual cost), in milliseconds.
Number of rows returned by the iterator.
Number of loops.
Example
Following is an example of the EXPLAIN statement with ANALYZE 
EXPLAIN ANALYZE SELECT * FROM CUSTOMERS;
It displays the output that includes more timing and cost-related details as shown below 
EXPLAIN
–> Table scan on CUSTOMERS (cost=0.35 rows=1) (actual time=0.070..0.070 rows=0 loops=1)
Example
First, let us insert values into the CUSTOMERS table created above using the INSERT statement 
INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 'Ahmedabad' ),
(2, 'Khilan', 'Delhi' ),
(3, 'kaushik', 'Kota'),
(4, 'Chaitali', 'Mumbai' ),
(5, 'Hardik', 'Bhopal' ),
(6, 'Komal', 'MP' ),
(7, 'Muffy', 'Indore' );
Let us create another table ORDERS, containing the details of orders made and the date they are made on 
CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUST_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);
Now, we are inserting some data into the ORDERS table as follows 
INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);
Following query deletes records from the above created tables 
SELECT * FROM CUSTOMERS 
INNER JOIN ORDERS ON ORDERS.CUST_ID = CUSTOMERS.ID;
We get the following output 
ID
NAME
ADDRESS
OID
DATE
CUST_ID
AMOUNT
3
Kaushik
Kota
102
2009-10-08 00:00:00
3
3000.00
3
Kaushik
Kota
100
2009-10-08 00:00:00
3
1500.00
2
Khilan
Delhi
101
2009-11-20 00:00:00
2
1560.00
4
Chaitali
Mumbai
103
2008-05-20 00:00:00
4
2060.00
To obtain information about this query's execution, you can use the EXPLAIN ANALYZE statement as follows
EXPLAIN ANALYZE SELECT * FROM CUSTOMERS 
INNER JOIN ORDERS ON ORDERS.CUST_ID = CUSTOMERS.ID\G;
The result produced is as follows 
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=2.05 rows=4) (actual time=0.117..0.145 rows=4 loops=1)
    -> Table scan on ORDERS  (cost=0.65 rows=4) (actual time=0.078..0.095 rows=4 loops=1)
    -> Single-row index lookup on CUSTOMERS using PRIMARY (ID=orders.CUST_ID)  (cost=0.28 rows=1) (actual time=0.010..0.010 rows=1 loops=4)
﻿
1 row in set (0.00 sec)
The explain_type Option
You can also specify the format in which you want to retrieve the information using the explain_type option. It allows you to choose between TRADITIONAL, JSON, and TREE formats. These different formats provide the same information but in a more structured manner for your analysis.
Example
In here, we are retrieving the information in TREE format using the explain_type option 
EXPLAIN ANALYZE FORMAT = TREE SELECT * FROM CUSTOMERS 
INNER JOIN ORDERS ON ORDERS.CUST_ID = CUSTOMERS.ID;
Following is the output obtained 
-> Nested loop inner join  (cost=2.05 rows=4) (actual time=0.111..0.136 rows=4 loops=1)
    -> Table scan on ORDERS  (cost=0.65 rows=4) (actual time=0.073..0.089 rows=4 loops=1)       
    -> Single-row index lookup on CUSTOMERS using PRIMARY (ID=orders.CUST_ID)  
Now, we are retrieving information in JSON format 
EXPLAIN FORMAT = JSON SELECT * FROM CUSTOMERS;
After executing the above code, we get the following output 
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.95"
    },
    "table": {
      "table_name": "CUSTOMERS",
      "access_type": "ALL",
      "rows_examined_per_scan": 7,
      "rows_produced_per_join": 7,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.70",
        "prefix_cost": "0.95",
        "data_read_per_join": "1K"
      },
      "used_columns": [
        "ID",
        "NAME",
        "ADDRESS"
      ]
    }
  }
} 
﻿
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	ADDRESS	OID	DATE	CUST_ID	AMOUNT
3	Kaushik	Kota	102	2009-10-08 00:00:00	3	3000.00
3	Kaushik	Kota	100	2009-10-08 00:00:00	3	1500.00
2	Khilan	Delhi	101	2009-11-20 00:00:00	2	1560.00
4	Chaitali	Mumbai	103	2008-05-20 00:00:00	4	2060.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

EXPLAIN

The MySQL EXPLAIN Statement

Syntax

Example

Output

EXPLAIN and ANALYZE

Example

Example

The explain_type Option

Example