MySQL SQL Query Optimization and Database Tuning for Beginners
It is not easy to find highly optimized SQL queries and well-structured databases. You might have the task to design a DB or to improve the performance of queries, either way, you need to be prepared to handle the challenges. MySQL SQL Query Optimization and database tuning are important skills that will help you make the application perform better.
In this article, we will go through what these tuning and optimization techniques mean. I will demonstrate some basic examples. The scope of this article is for beginners, but towards the end, we will talk about how to learn other skills such as advanced MySQL query tuning.
What is SQL Query Optimization
Query optimization in DBMS or query tuning is the process of altering the query to make it run faster. Often, the queries which are initially written aren’t designed for scale. This leads to the query slowing down the application as the amount of data increases.
As a beginner, you must understand some of the basic concepts behind an SQL query. This will help you identify potential problems with queries that you are tuning. Following are some of the SQL Optimization Techniques:
- The very first technique is to check the select statement. Very often, developers tend to use the “*” in a select query.
Example:
SELECT * FROM EMPLOYEE;
What you must understand here is that the “*” should be used only when all the fields need to be fetched.To make the query faster, mention only the fields that need to be fetched. Example:
SELECT FIRST_NAME, AGE, GENDER FROM EMPLOYEE;
- Using the operators IN, EXISTS appropriately. You must understand when each of them has to be used. It has been found that using “IN” is the slowest when it comes to query execution time. Following is an example of how you can perform SQL query optimization when you find a query having the IN operator.
SELECT NAME, AGE, GENDER FROM EMPLOYEE
WHERE EMP_ID IN ( SELECT EMP_ID FROM MANAGERS)
Now, instead of the above query, you can use EXISTS as follows.
SELECT NAME, AGE, GENDER FROM EMPLOYEE E
WHERE EXISTS ( SELECT EMP_ID FROM MANAGERS M
WHERE M.EMP_ID = E.EMP_ID)
- You can also use simple MySQL query optimization tools like the “EXPLAIN” operator. This operator, when added to the beginning of a query, gives you a sense of time the query will take to execute. It might not be fully accurate, but as a beginner, this will be very useful to you. The output of this query is called “QUERY PLAIN”. Following is an example:
EXPLAIN
SELECT * FROM EMPLOYEE
WHERE SALARY> 100000;
This will give you a cost associated with the query and higher the cost indicates, longer run time.
What is Database tuning
There are many things which overlap Database tuning and SQL query optimization. These two concepts are interrelated. However, the database tuning refers to the way in which database needs to be designed, and how to choose the right DBMS application and how to best set up the DB environment. Though this skill comes with time and experience, there are a few things you can look out for that would make your database more efficient.
- The very first thing you do is Normalize the database. Database Normalization is the process of removing redundant columns or to restructure the tables. Normalization helps to decrease anomalies that tend to occur when various queries are executed. The normalization process is divided into 4 different forms each addressing the various ways in which redundant data can be identified and used.
- Creation of optimized indexes. Index creation is a tricky subject. No indexes will lead to queries being slow and too many indexes will slow down the DML queries ( queries to insert, update and delete). This means that indexes must be created only when it is required. In my upcoming webinar series: “Advanced MySQL query tuning”, I take you through each task of SQL query optimization step by step. Enrolling in the course gives you a lot of benefits that can boost you to the next level.
- DB statistics tools provide information on table indexes and their distribution. These statistics help the DBA to find paths that not only satisfy a query but is also inexpensive.
Conclusion: what to do next?
We saw how you can quickly identify some of the most common mistakes developers or DBAs make. You can identify such anomalies and correct them to improve the database performance.
These, however, were just the basics. A drop in the ocean. As a DBA or someone performing SQL Query Optimization or Database tuning, there is much more to learn and apply. Over the 17 years in the industry, many of the Fortune 500 companies have hired me as a performance expert consultant and I have successfully helped them in overcoming their challenges.
Eric Vanier
Database PerformanceTechnical Blog Writer - I love Data
