MySql Query Optimization Techniques and Performance Tuning

Query Optimization and technique is a feature of RDBMS(Relational Database Management System). The main purpose of Query Optimization is to reduce the usage of CPU resource and execute the Query faster for the User access. 

  

As we know single query can be written in multiple ways, So Optimization technique use the best way which consume lesser system resource and provide faster data access to the users. Query Optimization Make Application response faster and enhance the performance. Now we will go through the query optimization technique step by step.

Avoid so many column in the Table

It's not a good practice of Having large number of column ( >100 ) in single table which  start impacting the query performance and consume the system resource at the time of Query Execution. If you need more column in that case you must break the table based on the logic. Normalization Database Design Technique comes in picture which enhance the system performance.

Avoid Using Select *  

Using Select *  pulls all the column even if it is not not required which consume the resource. Imagine you have  a table which consist more then 100 column and laks or millions of record ( rows ). Querying all the record (Column) using Select * even if all the column is not required is wastage of system resource. If you use actual column what is required for the application instead of all column then your Query execution become faster.

SELECT * FROM `studetnInfo` (This will pull all the column of the studetnInfo Table)

SELECT firstName, lastName, dob, emailId  FROM `studetnInfo`

Above query is there as a example for your reference. What column is needed we must pull that particular column.

Indexing a Column 

We must use DB indexing on the column which is used in SQL Clause such as where, order by and group by etc. Once Indexing is done it will help you in faster data retrieval and also help us in sorting the data.

 Once you create MySql Indexes it will consume space and also slow down the speed of INSERT, UPDATE and DELETE Query Command.

Example : Consider you have a Table named with studentDetail which contain laks of record and indexed on primary key column Named with ID. If we try to access the record using id the query execution will be faster because it will scan minimal row to get the exact data.

SELECT * FROM `studentDetail` WHERE id= 33333

We will use EXPLAIN on the query and try to know some information about the query.

EXPLAIN SELECT * FROM `student_details` WHERE id= 33333

If we will refer the above image which is the output of EXPLAIN query. In that there is column named with rows which is explaining how many rows are scanned before giving the query output.

Using EXPLAIN query we can get the information about the query and row scanned once the query execution happened.

Avoid Using Correlated Sub-queries 

Basically correlated Sub-queries depend on the outer query or parent query. Sub-query runs for each row-by-row return by the parent or Outer query which slow down the process and decrease the database efficiency.

SELECT Name, dob, (SELECT SchoolName FROM School WHERE ID = Student.schoolid) 
AS SchoolName FROM Student

In the above query each time outer Query will be executed the sub-query will also run row-by-row. which is bad practice of writing queries. We can write the above query in efficient way which is below for your reference.

SELECT st.name, st.dob, sc.SchoolName FROM Student st JOIN School sc ON sc.ID = st.schoolid

Avoid NULL Values in Column

We must avoid NULL Value in our database column because it harm the execution of the query and give unexpected result.

Avoid Storing HTML, XML, JSON or other Mark Up Language

Storing HTML, XML, JSON and other Mark Up Language in database will cost us, these Data is presentation data which must be handled by our Application. We must store data and rest of the presentation data must be handled by Application. Database is there for  Data Operation( Fetching, Inserting, Deleting, Updating ) not for rendering the web page or generating the document.

Use EXPLAIN to analyze your Query

Whenever query is executed the mysql optimizer uses Execution plan. EXPLAIN will give you all the information of execution plan. You can use this EXPLAIN in the beginning of the query. Example is below for your reference.

EXPLAIN SELECT * FROM `student_details` ORDER BY student_name DESC LIMIT 10 

id	1
select_type	SIMPLE
table	student_details
type	all
possible_keys	(NULL)
key	(NULL)
key_len	(NULL)
ref	(NULL)
rows	40716
Extra	Using filesort

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