MySQL Explain Query and Execution Plan Example

EXPLAIN in MySql is one of the strongest feature tool through which we can analyze and optimize the Query and we can rewrite in a cost efficient and resource Optimized way which can execute faster.

Basically EXPLAIN will give you the information about the Query Execution Plan which is used. Means it will give the information about the way through which Database execute Query. It can be used in front of SELECT, INSERT, UPDATE, DELETE and REPLACE. It is a feature tool through which we can find the Quick remedy for Slow Query.

If your MySql EXPLAIN Query is not working or not Parsing correctly then you can type SHOW WARNINGS into your editor and run it.

Use  EXPLAIN to Improve Query Performance

Prameter	Purpose
id	SQL Query id
select_type	Type of SQL Statement
table	Referenced Table
type	Type of Join (It defined how the table will be accessed or joined)
possible_keys	Possible key which can be  used in the Query
key	Key which is used in the Query
key_len	Used Key length in the Query
ref	Columns which got compared to indexes
rows	Numbe of rows scanned during query execution
Extra	Additional Information about Query

Consider the below Query (We have studentDetails Table which have millions of record in it ) :

EXPLAIN SELECT * FROM `studentDetails`

id	1
select_type	SIMPLE
table	studentDetails
type	ALL
possible_keys	(NULL)
key	(NULL)
key_len	(NULL)
ref	(NULL)
rows	40846
Extra

Above will be the output of the Query which we tried Executing using EXPLAIN. Usually EXPLAIN will give you lot of information regarding your query like which key are used, Indexing is used or not, how many rows are getting scanned while executing query, what are all the possible keys we can use in query etc.. We will study the above in detail below.

We will go through the each id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra column parameter in detail. And last we will analyze the Explain Query Example. 

select_type ( Type of SQL Statement ) :  Possible value which can come based on the Query is below

• SIMPLE
• PRIMARY, DERIVED 
• SUBQUERY 
• DEPENDENT SUBQUERY 
• UNCACHEABLE SUBQUERY 
• UNION 
• DEPENDENT UNION 
• UNION RESULT

Most important point to look in the EXPLAIN Query output is type and rows. Row gives you the information about how many rows are scanned before the query output. Type define how the table will be access or joined. We will see EXPLAIN Query type  in detail below.

• system : The table contain one or Zero rows.
• const : It is fastest type of join. It will have only one matching rows which will be indexed. It is fastest because table is scanned once and the column is treated as constant while joining other table.
• eq_ref :  In this Index (Primary or Unique NOT NULL) will be used by the join and it is the best table join type. One rows will read from the table and compared with rows from the previous table.
• ref
• fulltext :  This join will use FULLTEXT index for processing the query.
• ref_or_null
• index_merge
• unique_subquery
• range : Only rows belong to some particular or specified range get scanned for the query output. DB indexing is used to select or find the matching rows.
• index : In this all Index Tree will be scanned to find the exact matched query condition. 
• all : Entire Table row and its combination will be scanned to find the matched query condition output. 

Now we can see some Example :

EXPLAIN SELECT * FROM users WHERE id >60000 AND id < 60002

Prameter	Value
id	1
select_type	SIMPLE
table	users
type	range
possible_keys	id,registered_user,id_attyp
key	id
key_len	4
ref	(NULL)
rows	1
Extra	Using where

SQL Query Example and Sample SQL Commands Queries

MYSQL Query Example

CHAR_LENGTH() Example

CHAR_LENGTH() function is used to count the character in the given String.

⇛  SELECT question, explainans, `view`, CHAR_LENGTH(explainans) AS lengt 

FROM `question` WHERE explainans IS NOT NULL AND CHAR_LENGTH(explainans) > 200 
ORDER BY `view` DESC

---

GROUP BY() Example

⇛ SELECT created_at, COUNT(*) FROM `application_for_otherexam` WHERE exam_id = '8' 

GROUP BY DATE(created_at)

⇛ SELECT created_at, COUNT(*) AS Total FROM `application_for_otherexam` 

WHERE exam_id = '8'  AND form_status='55' GROUP BY DATE(created_at)

---

General Sample MY SQL Query Example

⇛ SELECT COUNT(*) AS Total FROM `application` WHERE formstatus IN('4', '44', '55') 

AND examid='8'

⇛ SELECT school_name, code FROM `school_master` WHERE code IN('22','31','31')

⇛ SELECT CONCAT(FIRST_NAME,' ',LAST_NAME) AS WORKER_NAME FROM WORKER WHERE 

CONCAT(FIRST_NAME,' ',LAST_NAME) LIKE '%hal'

⇛ SELECT YEAR(dob), COUNT(*) AS TOTAL FROM STUDENT_DETAILS GROUP BY YEAR(DOB) 

HAVING TOTAL>10 ORDER BY YEAR(DOB) ASC

⇛ SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME), SALARY FROM WORKER WHERE SALARY

BETWEEN 50000 AND 100000 

⇛ SELECT APPCOMMADDR1, LENGTH(APPCOMMADDR1) AS T1, APPCOMMADDR2, 
LENGTH(APPCOMMADDR2) AS T2, APPCOMMADDR3, LENGTH(APPCOMMADDR3) AS T3 
FROM `Data_Dump_Dept_exam_dec2019_20191203` HAVING T1>50 AND T2>50 AND T3>50

---

MYSQL Sub Queries Example

⇛ SELECT udiscode FROM schoolmaster WHERE code IN ( SELECT SCHL FROM `udise_mapping`

GROUP BY SCHL HAVING COUNT(*)>1)

---

CONCAT(), UPPER(), LOWER() Function Example

⇛ SELECT UPPER(FIRST_NAME) AS FNAME, UPPER(LAST_NAME) AS LNAME, 

CONCAT(UPPER(FIRST_NAME),' ',UPPER(LAST_NAME))AS FULLNAME FROM WORKER 

ORDER BY FULLNAME ASC

⇛ SELECT CONCAT(FIRST_NAME,' ',LAST_NAME) AS WORKER_NAME FROM WORKER WHERE 

CONCAT(FIRST_NAME,' ',LAST_NAME) LIKE '%hal'

---

SQL DATE(), YEAR(), MONTH() Function Example

⇛ SELECT CURDATE() 

⇛ SELECT YEAR(CURDATE())

⇛ SELECT MONTH(CURDATE())

⇛ SELECT DAY(CURDATE())

⇛ SELECT NOW()

⇛ SELECT YEAR(NOW())

⇛ SELECT MONTH(NOW())

⇛ SELECT HOUR(NOW())

⇛ SELECT MINUTE(NOW())

⇛ SELECT SECOND(NOW())

⇛ SELECT * FROM WORKER WHERE MONTH(JOINING_DATE) = 6 ORDER BY SALARY DESC LIMIT 2

⇛ SELECT  MONTHNAME(STR_TO_DATE(MONTH(dob),'%m')) AS mname, COUNT(*) AS totalno 

FROM `student_details` WHERE MONTH(dob) =1 GROUP BY MONTHNAME(STR_TO_DATE(MONTH(dob),'%m'))

---

Example Using Case in MYSQL Query

⇛ SELECT COUNT(
       CASE
           WHEN roll_no IS NULL
           THEN 1
           ELSE NULL
       END
   ) AS 'not_complete',
   COUNT(
       CASE
           WHEN  roll_no IS NOT NULL
           THEN 1
           ELSE NULL
       END
   ) AS 'completed'
    FROM `application`
WHERE examid =7

⇛ SELECT school_id, COUNT(
       CASE
           WHEN formstatus = '4'
           THEN 1
           ELSE NULL
       END
   ) AS 'Initiated',
   COUNT(
       CASE
           WHEN  formstatus = '45'
           THEN 1
           ELSE NULL
       END
   ) AS 'Payment Pending',
   COUNT(
       CASE
           WHEN  form_status = '55'
           THEN 1
           ELSE NULL
       END
   ) AS 'Payment Done'
    FROM `application` 
WHERE examid =7 GROUP BY school_id

⇛ SELECT COUNT(
CASE 
  WHEN gender = 'MALE'
  THEN 1
  ELSE NULL
  END 
  ) AS MALECOUNT, 
COUNT(
CASE 
  WHEN gender = 'FEMALE'
  THEN 1
  ELSE NULL
  END 
  ) AS FEMALECOUNT, COUNT(*) AS Total 
FROM `application` WHERE exam_id='8' AND form_status IN('55')

---

Using MYSQL IF statement with Example

⇛ SELECT b.centre_id,COUNT(IF(MED='TAMIL' OR  MED='ENGLISH',1,NULL)) AS TAMENG,
COUNT(IF(MED='KANNADA',1,NULL)) AS KANNADA,
COUNT(IF(MED='HINDI',1,NULL)) AS HINDI,
COUNT(IF(MED='URDU',1,NULL)) AS URDU,
COUNT(IF(MED='ARABIC',1,NULL)) AS ARABIC,
COUNT(IF(MED='PERSIAN',1,NULL)) AS PERSIAN,
COUNT(IF(MED='LATIN',1,NULL)) AS LATIN,
COUNT(IF(MED='ENGLISH',1,NULL)) AS ENGLISH FROM `dummynoexam`a
JOIN `examrollnumber` b ON a.student_id=b.student_id AND a.class_id=b.class_id 

AND a.sess_id=b.sess_id
JOIN  centremasterexam cm FORCE INDEX(cent_class_sess) ON   b.centre_id=cm.CENT 

AND b.class_id=cm.CLASS_ID AND b.sess_id=cm.SESS_ID
GROUP BY SUB,b.centre_id

⇛ SELECT COUNT(IF(statusflag = 1, 1, NULL)) AS STATUS1, COUNT(IF(statusflag=0, 1, NULL)) 
AS STATUS0, COUNT(IF(statusflag IS NULL,1, NULL)) AS STATUSNULL FROM `student`

---

MYSQL LPAD AND MID FUNCTION() USAGE and Example

SELECT `code`, MID(district_name,1,4) AS prefix, CONCAT(prefix,LPAD(`code`,4,0)) 
FROM `educational_district`

---

Set the Variable in MYSQL

SET @a:=1;  // Run this first and then run the query

SELECT e.code, s.dge_code, centre_id, rollno, student_group_id, subject_id, medium_id, @a:=@a+1 AS ID   FROM `student_pappermapping` a JOIN `student_exam_details` b ON a.student_exam_details_id =b.id
JOIN `student_details` c ON b.student_id= c.id 
JOIN `subject_list` d ON d.SUBJECT = a.subject_id AND b.class_id=d.CLASS_ID
JOIN school_master s ON c.school_id=s.id
JOIN `educational_district` e ON s.district_id=e.id
JOIN  `exam_rollnumber` r ON r.student_id=c.id AND r.class_id = b.class_id AND r.sess_id=b.sess_id
WHERE c.school_id =1345 AND b.class_id =3 AND b.sess_id= 1 AND (d.TTAG='P' OR LNKSUB <> '')  
ORDER BY e.code, dge_code, student_group_id, subject_id, medium_id

---

SELECT sr.student_id, doe.ROLLNO, doe.NAME, COUNT(*), (GROUP_CONCAT(sr.subject_id SEPARATOR ', ')) AS applied_subjects
FROM `student_revaluation` sr JOIN `dummy_no_exam` doe ON doe.student_id = sr.student_id AND doe.SUB = sr.subject_id GROUP BY sr.student_id

MySql Date Function and Query Example

MySql Date() Function List for Your Reference with Query Example. Consider Below table for Executing all the MySql Date Function.

Consider Below table Named with  Personal_Detail

FULLNAME	DOB	CREATED_AT
MUNISHA D	31-10-1996	14-11-2019 09:43
ANANTHA KUMAR C	20-08-2008	14-11-2019 10:12
THIRUMALAIKUMAR	28-06-2003	14-11-2019 09:53
DHANALAXSHMI S	10-06-2004	14-11-2019 10:11
AGNES LIDIA A	21-04-2003	14-11-2019 00:14
MOHANASRI G	28-05-2004	14-11-2019 10:12
PRIYANKA P	12-05-2001	14-11-2019 09:44
RANJITHKUMAR R	02-07-2004	14-11-2019 10:11
SABESH S	03-06-2005	14-11-2019 09:54
AMSAVALLI E	07-06-2004	14-11-2019 10:02

We will Write query to extract the data using MySql Date() function. Kindly go through the basic and view the query in the last for enhancing your SQL Skills.

MySql Date() Function with Example

---

⧉   CURDATE()  MySql Function : Once the below query will run it will O/P The current Date.

  SELECT CURDATE()    

   O/P : 2020-04-29  

---

⧉   CURTIME()  MySql Function :  Below Query will give you the Current time detail whatever time it is. Time will be in "HH-MM-SS" (string Format)

    SELECT CURDATE()    

    O/P : 12.52.09 

---

MySql Current TimeStamp Function 

---

⧉   CURRENT_TIMESTAMP()  MySql Function :  CURRENT_TIMESTAMP will return the Date with time

    SELECT CURRENT_TIMESTAMP()    

    O/P : 2020-04-29 19:44:07

---

⧉   DATE()  MySql Function :  DATE() function will be used to compare the date or to extract the date.

      SELECT  DATE("2020-04-29  06:34:21")

      O/P : 2020-04-29 

---

⧉   MONTH()  MySql Function :  MONTH() function will be used to extract the month from the given Date.

      SELECT  MONTH("2020-04-29  06:34:21")

      O/P : 4 

---

⧉   DAY()  MySql Function :  DAY() function will be used to extract the DAY from the given Date.

      SELECT  DAY("2020-04-29  06:34:21")

      O/P : 29 

---

⧉   DATEDIFF()  MySql Function :  DAYDIFF() function will compare the two date and return the number of day. Syntax : DATEDIFF(dateSel1, dateSel2)

      ⇒ SELECT  DATEDIFF("2020-10-29", "2020-04-29")

      O/P : 183

      ⇒ SELECT DATEDIFF( "2021-10-29" , CURDATE() )

      O/P : 548

       ⇒ SELECT DATEDIFF( "2021-07-29  09:34:21" , CURRENT_TIMESTAMP() )

      O/P : 456

---

⧉   DATE_ADD()  MySql Function :  DATE_ADD() function will add DAY, TIME, MONTH in the given date. Syntax : DATE_ADD(dateSel1, INTERVAL value  UnitAdd). UnitAdd can be DAY, WEEK, MONTH, YEAR, SECOND, MINUTE, HOUR etc..

      ⇒ SELECT  DATE_ADD("2020-10-29", INTERVAL  10  DAY )

      O/P : 2020-11-08

      ⇒ SELECT  DATE_ADD("2020-10-29", INTERVAL  10  MONTH )

      O/P : 2021-08-29

      ⇒ SELECT  DATE_ADD( CURRENT_TIMESTAMP() , INTERVAL  10  MONTH )

      O/P : 2021-02-28 20:08:34

---

⧉   DATE_SUB()  MySql Function :  DATE_SUB() function will substract DAY, TIME, MONTH in the given date. Syntax : DATE_SUB(dateSel1, INTERVAL value  UnitSub). UnitSub can be DAY, WEEK, MONTH, YEAR, SECOND, MINUTE, HOUR etc..

      ⇒ SELECT  DATE_SUB("2020-10-29", INTERVAL  10  DAY )

      O/P : 2020-10-19

      ⇒ SELECT  DATE_SUB("2020-10-29", INTERVAL  10  MONTH )

      O/P : 2019-12-29

      ⇒ SELECT  DATE_SUB( CURRENT_TIMESTAMP() , INTERVAL  10  MONTH )

      O/P : 2019-06-29 20:12:43

---

⧉   DATE_FORMAT()  MySql Function :  Know Basic Prefix for DATE_FORMAT()

• "%D" : It will return the day in numeric form with suffix like 1st, 2nd, 3rd, etc.  
• "%d"  : It will return the numeric value without suffix like 1, 2, 3, 4,..
• "%M" : It will return the month name fully.
• "%m" : It will return the month in numeric form.
• "%Y" : It will return the full year in four digit 2020, 2019, 2018 etc..
• "%y"  : It will return the year in two digit 20, 19, 18, 17 etc..

     
      ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%D" )
      O/P : 29th

      ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%d" )
      O/P : 29

       ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%M" )
      O/P : April

       ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%m" )
      O/P : 04

       ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%Y" )
      O/P : 2020

       ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%y" )
      O/P : 20

       ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%Y  %M" )
      O/P : 2020 April

       ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%Y  %M %D" )
      O/P : 2020 April 29th

      ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%Y /%m/%d" )
      O/P : 2020/04/29

---

⧉   NOW()  MySql Function :  It will return the current time and stamp in the format of "YYYY-MM-DD HH.MM.SS". NOW() Function and CURRENT_TIMESTAMP() will return the same value. 

  ⇒ SELECT NOW( )

      O/P : 2020-04-29 22:09:57