MySQL Query Optimization - Optimize SELECT statements

1. MySQL query optimization Optimize SELECT statements MySQL query optimization is one of the most frequent processes carried out by DBAs, and DB developers around the world. No databases are spared from performance issues and these optimizations help the DB to run at optimal performance levels. As a database administrator, you might have witnessed the extent to which “SELECT” queries are used for any given application. These statements are one of the leading causes of slow queries, long load times, and they can also cause bottlenecks in the database server. 

2. What is MySQL Query Optimization MySQL query optimization is a process in which we speed up the execution of SQL queries. The speed at which a query runs depends on many factors such as hardware resources, number of tables to scan, the volume of data ( total number of rows to scan), number of queries being run simultaneously, the joins that need to be performed, column indexes and more. Hence there are many metrics or dimensions to consider here while tuning the query. 

3. SELECT Statement Optimizations Now, let’s look at the various types of optimizations we can do.

4. Joining tables in queries is very common. MySQL provides two types of Joins. The Inner JOIN and the outer JOIN clauses. Inner joins are almost always faster to run when compared to an outer join like the LEFT OUTER JOIN for example. Compared to inner join, a LEFT JOIN has to also look at null-extending rows that can return more number of rows making the outer join slower. In order to optimize the SELECT statement with an OUTER JOIN, you can try to convert it to an inner join.  Consider the following example: SELECT * FROM Employee LEFT JOIN Administrator ON (EmployeeId) WHERE Administrator.tenure=3; In the example above, we have a LEFT OUTER JOIN being performed. In this case, if the WHERE condition leads to false every time for the generated NULL row, then we can convert this to an inner join query as follows:  SELECT * FROM Employee, Administrator WHERE Administrator.tenure=3 AND Employee.EmployeeId=Administrator.EmployeeId; This change can not only speed up the query but also free up some resources faster. MySQL query optimizations don’t necessarily mean changing the query, It can also be about restructuring the query with existing elements to make it run faster. Outer Join simplification is an example of such a restructuring technique. Next, we will look at Nested Join optimization. Any kind of nested logic tends to take more time because the number of comparisons that are carried out increases exponentially. Thus it becomes important to optimize these select statements with nested outer joins.  So, for a query with nested INNER JOINS, we can easily remove the parentheses and the joins can be evaluated from left to right. Apart from this, the order in which the tables are evaluated doesn’t matter either. 1. Join Optimizations

5. Under the WHERE clause optimization of SELECT statements, we will look at a series of checks you can perform. We will also look at a few changes that do not really contribute to our process much. Finally we will look at optimizations that come out of the box when WHERE conditions are used. When dealing with a WHERE clause, you can safely go for readability and understandability over making it more compact. The query optimizer is smart enough to make the query compact before running it and so we do not have to compromise on the maintainability of the query. For example, if you want to get rid of parenthesis, then you don’t have to because the query optimizer will do it before executing the query. Constant expressions that use the indexes are evaluated only once by the database so we don’t need to do any optimizations to it.  For a given condition in a query, if all the columns in the index are numeric, then the database will not even check the datafile. It retrieves the result directly from the index itself. These queries tend to execute very fast. When there is a join condition, the WHERE condition is simplified in order to ensure it executes faster. At times when GROUP BY or any other aggregations are not used, any HAVING conditions are merged with the WHERE condition. Apart from that when the having condition doesn’t match, the rows are skipped before they are output.  2. WHERE clause Optimization

6. When an ORDER BY clause is used without a LIMIT, then the order of the queries can differ, and that is why it is important to define what the limit should be and add it to the query. When the fields that are being ordered are also in the index, there is a chance that the MySQL database uses the index to satisfy the ORDER BY clause rather than executing a file sort operation. This can make the query run faster. While processing a query, there is a chance that the DB uses only apart of the index keys while fetching the GROUP BY columns and this is called a Loose Index Scan. There are several conditions that need to be met for this to happen with the most important criteria being that the query should be over a single table. On the other hand, is the Tight Index Scan. A tight index scan goes through the entire range of the index or most of the values in the index. The idea here is that both tight and loose index scans take lesser time than creating a temporary table while evaluating GROUP BY clauses. Thus, if it is possible to create conditions that satisfy either of the scans, we should be going for that.  Conclusion MySQL query optimization has many features that we can tackle. Specifically with the SELECT statements, the optimizations aim to make the query run faster. Here, not all the optimizations are changes that are carried out on the query. Some optimizations are carried out by the database while evaluating the queries. To ensure that the inbuilt optimizations are done correctly, we need to verify if the conditions for those optimizations are met while designing the queries. When queries are well developed, they need less maintenance in the future when the data volume grows. However, if we are trying to optimize slow queries, then we must consider the techniques we discussed to improve the performance by changing the design wherever possible.  3. ORDER BY and GROUP BY optimization

7. Can you Optimize your SQL query for best execution time? visit at www.optimizsql.com