How to Tune SQL Statements with Rewrite and Hints Injection for MySQL?

sql tuning for MySQL

There are some SQL statements with performance problem have to be tuned by SQL syntax rewrite and Hints injection, it is a little bit difficult for SQL tuning newcomers to master this technique. Developers not only have to understand the relationship between SQL syntax and the final query plan generation but have to understand the usage of optimizer hints and its limitations. Sometimes these two tuning techniques application will affect each other in a complex SQL statement.

Here is a simple example SQL that retrieves data from EMPLOYEE and DEPARTMENT tables.

select  * from employee,department
where emp_dept=dpt_id
   and emp_dept<‘L’
   and emp_id<1500000
   and emp_salary= dpt_avg_salary
order by dpt_avg_salary

Here the following are the query plans of this SQL, it takes 7.7 seconds to finish. The query shows a “Full Table Scan Department” and nested loop Employee table with a Non-Unique Key Lookup EMPS_SALARY_INX.

You can see that this SQL cannot utilize index scan even though the dpt_dept is an indexed field. It is because the condition emp_dept<‘L’ is not explicitly induced the condition dpt_id < ‘L’ although emp_dept=dpt_id is also listed in the where clause.

To enable the index search of Department table, I explicitly add a condition dpt_id < ‘L’ to the SQL statement as the following:

select   *
from  employee,
where  emp_dept = dpt_id
     and dpt_id < ‘L’
     and emp_dept < ‘L’
     and emp_id < 1500000
     and emp_salary = dpt_avg_salary
order by  dpt_avg_salary

Here is the query plan of the rewritten SQL and the execution time is reduced to 3.4 seconds. The new query plan shows that an Index Range Scan is used for the Department table and nested loop Employee table.

You may find that the nested loop to Employee by EMPS_SALARY_INX lookup may result into a lot of random access to the Employee table. Let me add a BKA hint to ask MySQL to use ‘Batched Key Access’ to join the two tables.

select   /*+ QB_NAME(QB1) BKA(`employee`@QB1) */ *
from  employee,
where  emp_dept = dpt_id
     and dpt_id < ‘L’
     and emp_dept < ‘L’
     and emp_id < 1500000
     and emp_salary = dpt_avg_salary
order by  dpt_avg_salary

The new query plan shows a Batched Key Access is used to join Department and Employee tables, you can BAK information from MySQL manual for details, the new plan takes only 1.99 seconds and it is more than 3 times better than the original SQL syntax.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 3 times faster than the original SQL.

5 Reasons Why a Hardware Upgrade can Worsen Performance

SQL Server query optimizer tool

After gaining approval to switch to new hardware (at last) and a newer SQL Server version, you start to make preparations. But what will you do if the performance worsens after you finally do the migration?

Hardware upgrades are often preferred over MySQL SQL performance tuning but the transition may not necessarily take place smoothly. In this post, we will consider five of the most common reasons why a hardware upgrade may actually worsen performance, and how to identify these reasons.

Hardware Upgrade Over MySQL SQL Performance Tuning: 5 Things to Check

Using a SQL Server query optimizer tool might not be the best bet here since the hardware has been changed. In such cases, consider the five most common ways given below to troubleshoot this problem:

Check a Few Sys.configurations Settings

The default setting for “max degree of parallelism” is zero in SQL Server. This means all the cores get used by queries that go parallel, so your workload on the new instance may be facing tasks struggling for CPU usage due to this setting.

You can find out if this is the case by comparing all the configurations in sys.configurations between instances and searching for anything that stands out. Some users might accidentally set the lowest memory value for a query.

Investigate for Bottlenecks in Hardware Settings

Certain unexpected situations can lead to a few problems that might not get detected until after hardware upgrading or migration. For instance, the server might not be receiving sufficient power, which can affect CPU operation as well. They become less capable and therefore, show greater utilization when checked in Windows Task Manager.

Identification can be done in two ways:

  1. Keep an eye on the Windows System log. You may find messages related to improperly plugged power sources.
  2. Try running a free tool to gauge the clock speed of the processors and whether all of them are operational. Different from a SQL Server query optimizer tool, you can find one online nevertheless.
Check if Your Processors are on Power Saving Mode

It might not be the sole cause behind greater CPU usage but you can expect it to be one of the elements contributing to the issue, aside from a need for MySQL SQL performance tuning. Fortunately, this is something you can resolve rather quickly, so it’s certainly worth a glance.

This setting can be checked in the BIOS – disable power savings in case it is enabled. However, you may have to confirm the change with the help of a server management tool such as HP System Insight Manager and a planned outage that will give you time to make the desired change in the BIOS and reboot the server.

Check the New SQL Server Version for Stack Dumps

A stack dump, also known as a “15-second error” is responsible for leaving the storage unresponsive for fifteen seconds, followed by increased load.

To check for a stack dump, you will have to open the SQL Server error log and look for errors in the span of some days. You can filter using the terms “15 seconds” or “stack dump” to locate the culprit.

Inspect Factors Outside SQL Server for CPU Usage

Although you may have already looked into this, experts recommend double-checking to ensure the database professional doesn’t miss anything. You may be surprised to see a user testing with the server or using a SQL Server query optimizer tool. They may neglect to inform you – or forget to revert to the original settings. Sometimes, users may forget about previously running tasks while a new instance goes live.

To find out what went wrong, open the Windows Task Manager to take a look at all the processes that are utilizing memory and CPU. There shouldn’t be any agent tasks or scheduled jobs that were absent on the previous server.

How to Tune SQL Statements to Run SLOWER… but Make Users Feel BETTER (MySQL)?

MySQL database and SQL

Your end-users may keep on complaining about some functions of their database application are running slow, but you may found that those SQL statements are already reached their maximum speed in the current MySQL and hardware configuration. There may be no way to improve the SQL unless you are willing to upgrade your hardware. To make your users feel better, sometimes, you don’t have to tune your SQL to run faster but to tune your SQL to run slower for certain application’s SQL statements.

This is an example SQL that is used to display the information from tables Emp_subsidiary and Employee if they are satisfied with certain criteria. This SQL is executed as an online query and users have to wait for at least 5 seconds before any data will be shown on screen after the mouse click.

select  *
from    employee a,
         emp_subsidiary b
where   a.emp_id = b.emp_id
         and a.emp_grade < 1050
         and b.emp_salary < 5000000
order by a.emp_id

Here the following is the query plan and execution statistics of the SQL, it takes 5.48seconds to extract all 3645 records and the first records return time ”Response Time(Duration)” is 5.39 seconds. The query shows a “Full Table Scan b (emp_subsidiary)” to Nested-Loop “a (employee)” table, an ORDER operation is followed by sorting the returned data by emp_id. You can see there is a Sort Cost=7861.86 at the ORDER step on the query plan. It is the reason that users have to wait at least 5 seconds before they can see anything shows on the screen.

To reduce the sorting time of a.emp_id, since a.emp_id=b.emp_id, so I can rewrite the order by clause from “order by a.emp_id” to “order by b.emp_id”, MySQL now can eliminate the sorting time by using the EMPLOYEE_PK after the nested loop operation.

select  *
from    employee a,
         emp_subsidiary b
where   a.emp_id = b.emp_id
         and a.emp_grade < 1050
         and b.emp_salary < 5000000
order by b.emp_id

Although the overall Elapsed Time is higher in the new query plan, you can see that the response time is reduced from 5.397 seconds to 0.068, so the users can see the first page of information on the screen instantly and they don’t care whether there are 2 more seconds for all 3,645 records to be returned. That is why SQL tuning is an art rather than science when you are going to manage your users’ expectations.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically.

Optimization in SQL: Answering 4 Commonly-Asked Questions

optimization of sql queries

A SQL query or statement is tasked with fetching the required information from the database. While the same output can be gained from different statements, they are likely to work at different performance levels.

The difference in performance output makes a lot of difference because a millisecond of lapse in query execution can result in huge losses for the organization. This makes it extremely necessary to ensure the best statement is being used, which is where optimization in SQL is considered.

#1: What is Query Optimization in Databases?

Query optimization in databases is the general process of picking out the most efficient way of obtaining data from the database i.e. carrying out the best query for a given requirement. Since SQL is nonprocedural, it can be processed, merged, and reorganized as seen fit by the optimizer and the database.

The database enhances each query on the basis of various statistics gathered about the information fetched from it. On the other hand, the optimizer selects the optimal plan for a query after assessing different access techniques including index and full-table scans. Various join methods and orders are also used along with certain probable transformations.

#2: What is Query Cost in Optimization?

Query cost is a metric that helps examine execution plans and determine the optimal one. Depending on the SQL statement and the environment, the optimizer sets an estimated numerical cost for every step throughout potential plans and considers an aggregate to derive the overall cost estimate for it.

The total query cost of a query is the sum of the costs incurred at every step in it. Since query cost is a comparative estimate of the resources needed to carry out every step of an execution plan, it doesn’t have any unit. The optimizer picks out the plan with the least cost projection once it has completed all its calculations of all the available plans.

#3: Is Query Cost the Best Way to Judge Performance?

In a word: No. Why? Although query cost proves useful in comprehending the manner in which a specific query is optimized, we must bear in mind its main goal: helping the optimizer select decent execution plans.

It does not offer a direct measure of parameters such as CPU, IO, memory, duration that are significant to users waiting for a statement to finish running. In other words, a low query cost won’t necessarily mean the plan is optimal or the query in question is the quickest. Similarly, a high query cost can prove more efficient in comparison, which is why it is not recommended to depend too much on query cost when considering performance.

Being a CPU-intensive operation query optimization in SQL takes a lot of resources to determine the best plan among the ones present. Time also needs to be factored in here as the user may not always have the time it may take for this entire process to take place. 

Therefore, the resources required to optimize a statement, those required to run the statement, and the time it takes for all of this to be done with shouldn’t exceed each other. 

#4: How Can We Optimize a SQL Query?

Query optimization often needs extra resources, such as the addition of indexes. However, we can boost query performance by simply rewriting a statement to decrease resource consumption without further expenses.

This lets us save significant resources, money, and time (if a query optimization tool is used). Through query optimization in SQL, we can focus on specific areas that are causing latency instead of examining the entire procedure. In such cases, looking for sections that are taking up more resources will help us narrow down the search and fix issues more quickly.

How to Tune SQL Statements with CONCAT Operator for MySQL?

oracle sql performance tuning

There may be some business requirements that need to compare concatenate strings and column with a given unknown length of the bind variable. Here is an example SQL that retrieves data from EMPLOYEE and DEPARTMENT tables where employee’s department ID must concatenate two strings before it is compared to an unknown length of variable @dpt_var

select * from employee,department
where concat(concat(‘A’,emp_dept),‘B’) = @dpt_var
and  emp_dept= dpt_id

Here the following are the query plans of this SQL, it takes 23.8 seconds to finish. The query shows a “Full Table Scan Employee” to nested loop Department table.

You can see that this SQL cannot utilize index scan even the emp_dept is an indexed field. Let me add a “force index(EMPS_DPT_INX) hints to the SQL and hope it can help MySQL SQL optimizer to use index scan, but it fails to enable the index scan anyway, so I add one more dummy condition emp_dept >= ” , it is an always true condition that emp_dept should be greater or equal to a smallest empty character. It is to fool MySQL SQL optimizer that emp_dept’s index is a reasonable step.

select  *
from  employee force index(EMPS_DPT_INX),
where  concat(concat(‘A’,emp_dept),‘B’) = @dpt_var
     and emp_dept >= ”
     and emp_dept = dpt_id

Here is the query plan of the rewritten SQL and it is running faster. The new query plan shows that an Index Range Scan is used for Employee table first and then nested loop Department table.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 3 times faster than the original SQL.

MySQL SQL Performance Tuning: 8 Great Monitoring Practices

MySQL SQL performance tuning

DBAs don’t mind getting the attention of the management, as long as it’s for a positive reason. I mean once in a while, but not all the time; many would still prefer to remain off-radar, quietly ensuring the organization’s databases stay running without a hitch.

Since they have significant accountability in an organization, it is essential for them to beware of all the things that could go south. They can do this by following certain excellent practices for monitoring and MySQL SQL performance tuning, which we will explain in this post.

Best MySQL Database and SQL Monitoring Practices

With these practices, you can avoid being recognized by management for the database outage that took four days to fix, instead of your people skills.

  1. Conduct Regular Health Checks of Your Database

A database administrator knows how important it is to schedule regular health checks for their database. Note that every database has its own maintenance requirements, and that the health checks should be geared toward particular functional needs.

Databases that are non-critical do not require as frequent checks as mission-critical or life-critical databases. A local bookshop’s customer rewarding app failing isn’t as severe as the failure of a missile defense system, for instance.

  • Monitor MySQL Availability

This is perhaps the most essential metric to follow, since the unavailability of the database won’t leave much choice, nor will the other metrics matter until this particular issue is resolved. Use the Run dialog to check availability. You need to type in “ -mysqladmin -h -u root -p” to do so, and initiate diagnostics in case there’s a problem.

  • Check for Unsuccessful Connections & Error Logs

Monitoring the list of unsuccessful connections can slowly but surely help you identify both malicious activity and errors that aren’t as serious (caused by human error like incorrect id\password or misapplied permissions), in the application.

You are likely to get a broader picture in this manner, which will enable you to recognize larger recurring problems so that you can address them appropriately. You can keep track of failed connections by running the following command –

SHOW GLOBAL STATUS LIKE ‘aborted_connects’;

– to know the number of aborted connection attempts on the database in a provided time range.

  • Identify Deadlocks in InnoDB

In MySQL database and SQL, a deadlock takes place when multiple transactions put a lock on a resource required by another transaction. Deadlocks lead to retarded processing, increased timeouts – and unhappy users. Using the query “SHOW ENGINE INNODB STATUS;” will help you find deadlocks and fix them.

  • Observe Configuration Changes

An abrupt decline in performance can be the result of any number of causes. However, checking for any recent configuration changes can help you spot any adversely affecting ones and save you a considerable amount of time.

  • Keep an Eye On the Slow Queries Log

Queries that are slow make the database operate slower as well. This is due to an increase in CPU and memory usage. Assess the Slow Queries log from time to time to know if any queries are taking excessive time to run. You can then proceed towards identifying the root cause and resolving it. 

  • Maintain Visibility to Comprehend the Main Reason Behind Performance Issues

Although regular health checks are important for MySQL SQL performance tuning from the perspective of maintaining high availability, they are not as useful in terms of overall system troubleshooting.

This is because periodic performance concerns may not appear during a routine health check, which is also why a consistent visibility must be established with the MySQL environment.

The quicker you can uncover and fix performance related problems, the fewer the users that will be affected, since downtime will be considerably decreased. 

Setting alerts for critical occurrences can give you the chance to react as quickly as possible, in case a threshold is surpassed. You may set alert thresholds for these typical performance deterioration sources, at least –

  • Substantial deviations from baseline metrics, in terms of performance tuning in SQL MySQL
  • Excessive CPU utilization
  • Query latency
  • Query faults
  • Connection restraints
  • Buffer pool usage
  • Identify and Resolve Performance Issues Quickly

A database monitoring and MySQL SQL performance tuning tool is going to be the best option in nearly every case, so that issues are resolved before they grow into bigger problems.

There are plenty of performance tuning and monitoring tools on the market that come with a broad range of features at varying price ranges. Choosing the right one for your database will depend on your budget and requirements.

As long as you know what to look for in a performance tuning tool, you won’t have many issues in making your selection. Given below are a few features you should consider if you want a tool to perform SQL tuning for MySQL –

  • Scalability
  • Mobile tracking
  • Intuitive User Interface
  • Affordable yet feature-filled
  • Zero connection limitations
  • Different analysis variations, such as
    • Multidimensional workload analysis
    • Alarm source analysis
    • Blocking analysis
  • Smart alarms
  • Historical data monitoring

SQL Performance Tuning: Frequent Questions about Indexes

SQL performance tuning

A database is a piece of software operating on a computer, which means it is dependent and likely to face the same limitations as other software present on that computer. In other words, it will only be able to process as much data as the hardware can handle.

One of the best ways to speed up queries is to perform SQL performance tuning. In this post, we will answer some of the most frequent questions involving databases and indexes.

What is Indexing in SQL Query Optimization?

Indexing is one of the first things you may have come across while learning the ropes of your database. It is a wonderful tool that enables users to enhance the efficiency of their database. However, bear in mind that not every database requires indexing, and not all indexes are helpful in SQL performance tuning.

Let’s learn more about indexing: what it is and how it helps in enhancing database performance.

How do Indexes Affect SQL Query Performance?

An Index can locate data swiftly without having to go through each row in the table. This saves plenty of time! 

Certain data columns are required before you can create an index. These are –

  • The Search Key which holds a duplicate of the primary key
  • The Data Reference which has a set of pointers

All of these constitute the structure of one index. To understand how an index works, let us take an example. Suppose you need to look for a bit of data in your database. Rather than scour every line yourself, you make the computer search each row till it locates the information. Remember that the search is bound to take much longer if the requisite information is located at the end. Fortunately, you have the option to sort alphabetically to shorten the length of such queries.

What are the Types of Database Indexes?

Database indexes are of two kinds –

Clustered indexes – These arrange data using the primary key. The reason behind using a clustered index is to make sure the primary key is saved in ascending order. This is the same order in which the table stores memory.

A clustered index is automatically created when the primary key is set, which helps in SQL tuning for Oracle in the long run as well.

Non-clustered indexes – A non-clustered index is a data structure that boosts data fetching speed. It is different from clustered indexes, as they are made by data analysts or developers.

When and How Should We Use Indexes?

Since indexes are intended to accelerate database performance, you should apply them whenever you think they can simplify the use of the database. Although smaller databases may not have several opportunities to use indexes, they are likely to see the benefits of indexing as they grow into larger databases. 

You can make sure your indexes keep performing well, if you test run a set of queries on your database first. Clock the time those queries take to execute and begin creating your indexes after that. Keep rerunning these ‘tests’ for continuous improvements.


Indexing has its challenges, the biggest one being determining the best ones for every table.

For instance, heaps require clustered indexes because searching for a record in a heap table is comparable to finding a needle in a haystack: it’s inefficient and time-consuming, thanks to the heap’s unordered structure.

On the other hand, locating data is simpler and faster from a table that contains a proper clustered index, just like finding a name in a list that’s alphabetically ordered. DBAs, therefore, recommend that every SQL table contains a proper clustered index. Now that you know how indexes work and how they can optimize database performance, you should be able to use them to reduce query times substantially. If you would like more tips on how to use indexing, or you need a SQL query optimization tool for your database, let our experts know!

How to Tune SQL statement with Transitive Dependency Improvement for MySQL?

oracle query optimizer too

The following is an example shows a SQL statement with two conditions “emp_dept=dpt_id and emp_dept<‘L’”

select  *  from employee,department
where  emp_dept=dpt_id
  and  emp_dept<‘L’
  and  emp_id<1500000
  and  emp_salary= dpt_avg_salary
order    by  dpt_avg_salary

Here the following is the query plan of this SQL in Tosska proprietary tree format, it takes 8.84 seconds to finish.

The query plan looks reasonable that shows a full table scan of DEPARTMENT to nested-loop EMPLOYEE table, the records in EMPLOYEE table being nested-loop must satisfy with the condition “emp_id<1500000” and the corresponding index EMPS_SALARY_INX is also used. Due to the number of records in the first driving table in a Nested Loop Join is very critical to the join performance, we should find a way to narrow down the number of result records of DEPARTMENT table before it is used to nested-loop EMPLOYEE table.

As the conditions “emp_dept=dpt_id and emp_dept<‘L’”, it implies that “dpt_id < ‘L’” is also true, let me add this extra condition to the SQL, it helps MySQL SQL optimizer to make a better decision with more information provided by the new SQL syntax, this technique is especially useful for MySQL database.
Oracle or MS SQL Server are doing very good on their internal Transitive Dependency Improvement in their SQL optimizer already, so this technique may not work for Oracle and MS SQL Server.

select      *
from        employee,
where     emp_dept = dpt_id
    and dpt_id < ‘L’
    and emp_dept < ‘L’
    and emp_id < 1500000
    and emp_salary = dpt_avg_salary
order by dpt_avg_salary

Let’s see the DEPARTMENT is now being filtered by the new condition “dpt_id < ‘L’ “ with an index range scan. You can see the estimated Rows 401 of DEPARTMENT table is now being trimmed down to 176. The rewritten SQL now takes only 3.8 seconds with such a simple change in syntax.

This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that this rewrite is more than 2 times faster than the original SQL with such an easy change in the syntax.

How to Tune SQL Statement with Driving Path Control for MySQL?

sql tuning for MySQL

The following is an example shows a SQL statement with two potential table join paths.  “Employee to Department” and “Department to Employee” are potential driving paths which will be decided by MySQL SQL optimizer during SQL optimization stage.  

select  emp_id,emp_name,dpt_avg_salary
  from employee ,department
where emp_dept=dpt_id
and emp_dept like ‘A%’
and dpt_id like ‘A%’;

Here the following is the query plan selected by MySQL SQL optimizer in Tosska proprietary tree format, it takes 59 seconds to finish.

The query plan looks reasonable that uses DEPARTMENT’s Primary Key (DPT_ID) to fetch DEPARTMENT table first and then nested loop EMPLOYEE table by EMP_DEPT index, the speed of this query plan depends on the size of EMPLOYEE table and the records distribution according to the EMP_DEPT code.

If we want to change the driving path of the query plan from EMPLOYEE to DEPARTMENT, let me add a ifnull(dpt_id,dpt_id) dummy function to disable the DPT_ID index search, so it can artificially add cost to condition search DEPARTMENT table first. It means that using EMP_DEPT index search cost is relative cheaper now, so EMPLOYEE to DEPARTMENT driving path is probably be selected by MySQL SQL optimizer in the following:

select  emp_id,
from     employee,
where  emp_dept = dpt_id
        and emp_dept like ‘A%’
        and ifnull(dpt_id,dpt_id) like ‘A%’

EMPLOYEE to DEPARTMENT driving path plan is generated by MySQL now and it takes only 18.8 seconds only to finish the query.

If we know that using the EMP_DEPT index is not that efficient due to the selectivity of “ like ‘A%’ “ condition may not high enough to utilize the index range scan. Let me add an additional ifnull(emp_dept,emp_dept) dummy function to disable the EMP_DEPT index range scan too in the following:

select  emp_id,
from     employee,
where  emp_dept = dpt_id
        and ifnull(emp_dept,emp_dept) like ‘A%’
        and ifnull(dpt_id,dpt_id) like ‘A%’

Now, MySQL use full table scan of EMPLOYEE table to nested loop DEPARTMENT table. The speed is further improved to 15 seconds now.

This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the best rewrite is around 4 times faster than the original SQL.

How to Tune SQL Statement with Multiple Union in Subquery for MySQL?

dba tuning

The following is an example shows a SQL statement with two union operator in a subquery. The SQL retrieve records from EMPLOYEE table that EMP_ID should satisfy with the union result set from two queries in a subquery.

select * from employee
  where emp_id IN
  (select emp_id from emp_subsidiary where emp_grade=1000
    select emp_id from employee where emp_dept=‘AAA’)

Here the following are the query plans in Tosska proprietary tree format, it takes 3 minutes 27 seconds to finish.

The query plan shows a full table scan of EMPLOYEE table and the attached subquery will be executed for each of scanned record. So, you can see the query plan is very inefficient. If we know the union result set is small and it should be executed first, and then use EMP_ID index to retrieve EMPLOYEE table. Let me rewrite the Union subquery as a derived table expression in the following:

select *
from employee
where  emp_id in (select  emp_id
                       from    (select  emp_id
                             from     emp_subsidiary
                             where  emp_grade = 1000
                             select  emp_id
                             from     employee
                             where  emp_dept = ‘AAA’) DT1)

Now, you can see the Union subquery is executed first and use it to retrieve the EMPLOYEE table by EMP_ID index. The overall query is now become more reasonable and efficient.

This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 60 times faster than the original SQL.  There are some other rewrites with even better performance, but it is a little bit complicated to discuss in this short article, let’s discuss it in my coming blogs.