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