I’ll explore additional solutions for optimizing SQL queries that are use “NOTIN” subqueries for my recent blog post.
Below is an example SQL statement with a NOT IN subquery. It retrieves records from the grade table where grd_id does not match any emp_grade in the employee table for rows with emp_id greater than 3000:
select *
from grade
where grd_id not in (select b.emp_grade
from employee b
where b.emp_id > 3000)
In my last post, rewriting the original query to use NOT EXISTS achieved the best performance in my database. However, this approach is not universally optimal for all schema designs:
select *
from grade g1
where not (exists (select ‘x’
from employee b
where g1.grd_id = b.emp_grade and
b.emp_id > 3000))
I’ll now list other high-performance solutions:
Solution 1 – Adding a GROUP BY clause prompts the optimizer to sort and hash-aggregate the employee table first.
Solution 2 – Adding COALESCE(b.emp_grade, b.emp_grade) prevents potential index usage on the employee table, forcing a Seq Scan. This alters the join order from the original query’s execution path.
Solution 3 – Replacing the literal 3000 with a subquery (SELECT (3000)) prevents the optimizer from using an index on b.emp_id by obscuring the constant value.
Below is an example of a SQL statement using an NOT IN subquery. This query retrieves records from the Grade table where the grd_id does not match any grd_id in the employee table for rows where emp_id exceeds 3,000:
select *
from grade
where grd_id not in (select b.emp_grade
from employee b
where b.emp_id > 3000)
Here is the query plan; it takes an exceptionally long time 50 minutes and 10 seconds to complete.
The query plan shows a Materialize temp storage of the Seq Scan with Emp_id > 3000 on the Employee table and then filter by Grade table Grd_id. This query plan is bad since there are around 3 million records of employee tables that will be scanned for each one of five thousand rows of Grd_id from Grade table.
To address this, I rewrote the SQL to a NOT (EXISTS …) statement:
select *
from grade g1
where not (exists (select ‘x’
from employee b
where g1.grd_id = b.emp_grade and
b.emp_id > 3000))
The revised query plan shows that a Nest Loop from Grade table with an Index Scan(emps_grade_inx) of Employee table.
The rewritten SQL achieves 0.07 seconds execution time, demonstrating significantly improved performance.
The rewritten SQL is more than 43,000 times faster than the original SQL. This type of optimization can also be achieved using Tosska SQL Tuning Expert Pro for PostgreSQL. On the following screen, there are some SQL alternatives with better performance, but that are not suitable to discuss in this short article. Maybe we can discuss this topic later.
Below is a SQL statement from my recent blog post. A reader asked about the performance impact of adding +0 to a subquery’s select list (e.g., b.emp_grade+0). I was pleased to learn that this +0 solution was first introduced by me roughly 20 years ago to resolve a specific subquery issue. However, this method has since been replaced with COALESCE(b.emp_grade, b.emp_grade) because it avoids data type detection and prevents unexpected errors.
Today, I’d like to explore additional solutions for optimizing SQL queries using IN subqueries.
select *
from grade
where grd_id in (select b.emp_grade
from employee b
where b.emp_id > 3000000)
The following screenshot displays SQL alternatives generated by Tosska SQL Tuning Expert Pro using the Rewrite Only option and SQL Tuning Intelligence 2. I’ll share additional SQL rewrite methods to showcase the full spectrum of possibilities, hoping to inspire readers with the remarkable art of SQL tuning techniques
I won’t discuss the details of those SQL statements, but I’m happy to share the remaining query rewrites and their corresponding execution plans below:
Here the following is a simple SQL statement with a CASE expression syntax.
SELECT *
FROM employee
WHERE
CASE
WHEN emp_salary< 1000
THEN ‘low’
WHEN emp_salary>100000
THEN ‘high’
END = :a
The following are the query plans for this SQL statement, which completes in 1.01 seconds with the bind variable ‘:a’ equal to ’low’. The query involves a Full Table Scan of the EMPLOYEE table because the CASE expression cannot utilize the emp_salary index.
We can refactor the CASE expression into the following syntax using multiple OR conditions
select *
from employee
where (emp_salary < 1000 and
‘low’ = :a or
((not (emp_salary < 1000)) or
emp_salary is null) and
emp_salary > 100000 and
‘high’ = :a)
Rewriting multiple OR conditions with an AND operation (e.g., ‘low’ = :a) can efficiently disable unnecessary data searches on the EMPLOYEE table.
Here is the query plan of the rewritten SQL and the speed is 0.005 seconds. It is around 200 times better than the original syntax. The new query plan shows an Bitmap Index Scan on emp_salary index.
This kind of rewrite can be achieved by Tosska SQL Tuning Expert for PostgreSQL automatically, there are other rewrite with even better performance, but it is not suitable to discuss in the short article, maybe I can discuss later in my blog.
openGauss is an open-source relational database management system (RDBMS) that originated from PostgreSQL. It is designed for high performance, high availability, and enterprise-grade features. Initially developed by Huawei, openGauss was later open sourced to the community.
Here is a common problem that developers may encounter: writing a dynamic SQL statement with a CASE expression that hardcodes a = ‘low’ instead of using a bind variable like = :var, as shown below:
SELECT *
FROM employee
WHERE
CASE
WHEN emp_salary< 1000
THEN ‘low’
WHEN emp_salary>100000
THEN ‘high’
ELSE ‘Normal’
END = ‘low’
Below is the query plan for this SQL statement, which takes 2.20 seconds to complete. The query performs a sequential scan (Seq Scan) on the EMPLOYEE table because the CASE expression cannot utilize the emp_salary index.
We can rewrite the CASE expression using the following syntax with multiple OR conditions.
select *
from employee
where (emp_salary < 1000 and
‘low’ = ‘low’ or
(not (emp_salary < 1000)) and
emp_salary > 100000 and
‘high’ = ‘low’ or
(not (emp_salary < 1000 or
emp_salary > 100000)) and
‘Normal’ = ‘low’);
If the emp_salary field is nullable, the SQL query should be written like this:
select *
from employee
where (emp_salary < 1000 and
‘low’ = ‘low’ or
((not (emp_salary < 1000)) or
emp_salary is null) and
emp_salary > 100000 and
‘high’ = ‘low’ or
((not (emp_salary < 1000 or
emp_salary > 100000)) or
emp_salary is null) and
‘Normal’ = ‘low’)
Here is the query plan for the rewritten SQL, which executes in 0.002 seconds. This is 1100 times faster than the original syntax. The new query plan utilizes a Bitmap Index Scan on the emp_salary index.
This type of rewrite can be achieved automatically using Tosska SQL Tuning Expert Pro for openGauss. There are other rewrites that deliver even better performance, but they are not suitable to discuss in this short article. I may explore them in more detail in a future blog post.
The Query Rewriter Plugin in MySQL is a component that allows you to modify incoming SQL queries before execution. It provides the ability to transform, route, filter, or expand queries based on specific requirements. This plugin operates at the SQL layer and can be utilized for optimizing query performance, enforcing security policies, implementing data partitioning strategies, or adding additional business logic to queries. With the Query Rewriter Plugin, you have the power to customize and shape SQL queries to meet your specific needs, providing flexibility and control over query execution within the MySQL server.
The Query Transformation feature enables you to rewrite or transform the original query into an equivalent or more efficient form. This can be useful for optimizing performance, simplifying complex queries, or enforcing certain query plans.
You must install Query Rewriter Plugin before using this feature, the concept of Query Rewriter is simple, it is a set of predefined SQL statements that is used to replace a certain pattern of SQL statements that is fired from your application programs.
If you have installed the plugin, the following SQL statements can be used to defined your SQL replacement rules and error message handling.
INSERT INTO query_rewrite.rewrite_rules (message, pattern, replacement)
VALUES(Unique_ID, Original_SQL, Rewrite_SQL);
The query_rewrite.rewrite_rules table in MySQL stores the rules used by the Query Rewriter Plugin to rewrite SQL queries. The table has two columns:
Pattern – This column represents the pattern or condition that triggers the rewriting of a SQL query. It defines the specific query or query pattern to match.
Replacement – This column specifies the replacement or transformation that should be applied to the matched query or query pattern.
When a SQL query is executed, the Query Rewriter Plugin checks the query_rewrite.rewrite_rules table for matching patterns. If a pattern matches the executed query, the plugin rewrites the query using the corresponding replacement. This allows you to modify the query structure, optimize it, or add custom logic based on specific patterns or conditions.
I utilize the message column to define a temporary unique id for the SQL replacement rule, so the actual rule id can be extracted with the following SQL.
SELECT id into :SID FROM query_rewrite.rewrite_rules where message=Unique_ID;
When you make changes to the query rewrite rules in the query_rewrite.rewrite_rules table, those changes are not immediately applied. Instead, MySQL caches the rules in memory for better performance. However, if you want to ensure that the updated rules take effect immediately, you can call the query_rewrite.flush_rewrite_rules() function.
CALL query_rewrite.flush_rewrite_rules();
If a load error occurs, the plugin also sets the Rewriter_reload_error status variable to ON and the error message will be stored in the Message column.
SELECT message FROM query_rewrite.rewrite_rules where id=:SID;
Actually, the Query Rewriter Plugin is powerful and easy to use. The most challenging aspect is finding a replacement SQL for your poorly performing SQL statement. Tosska DB Ace Enterprise for MySQL can assist you in automating this process, from identifying poorly performing SQL statements to rewriting SQL syntax and deploying replacement rules.
The previous article “How To Use 80/20 Rule To Tune A Database Application I “ demonstrated how the 80/20 Rule can be applied to evaluate the overall performance of SQL workload in a database. In this example, a set of 90 SQL statements retrieved from Oracle SGA is presented in a chart that lists each statement based on its resource usage in descending order, with the most resource-intensive SQL on the left. The analysis reveals that roughly 14.44% of the SQL statements consume 80% of the total elapsed time, while 21.11% of the SQL statements consume 80% of the total CPU time, indicating that the SQL workload distribution aligns well with the 80/20 rule. Therefore, tuning the SQL may not be necessary since it is unlikely to result in significant performance improvements.
However, to further optimize the database performance cost-effectively, it is recommended to conduct an in-depth investigation of the top 20% of high workload SQL statements. This will reveal that the resource utilization drops steeply in the first few SQL statements, making them the most critical candidates for optimization.
Let’s aim to reduce the proportion of the total resource consumption from 80% to 60% and examine the SQL statements that are responsible for utilizing the resources. The results are interesting and reveal that 3 SQL statements account for 60% of the elapsed time, 6 SQL statements account for 60% of the CPU time, and only one SQL statement accounts for 60% of the disk reads. By focusing on these SQL statements, it is possible to enhance up to 60% of the database workload. For instance, if the database is experiencing an IO bottleneck, concentrating on the one SQL statement could yield savings of up to 60% on disk reads.
You can utilize Excel to conduct a simulation of the 80/20 rule analysis described above, providing a comprehensive overview of the distribution of the SQL workload. This approach facilitates a rapid evaluation of the overall health of the database’s SQL performance, as well as the associated costs and benefits of optimizing high workload SQL statements. Furthermore, the SQL resource spectrum analysis is integrated into our Tosska DB Ace for Oracle software.
Here is an example SQL, the query is retrieving employee, department, and grade tables from the remote database @richdb
SELECT *
FROM emp_subsidiary@richdb a,
department@richdb,
grade@richdb
WHERE emp_grade < 1200
AND emp_dept = dpt_id
AND emp_grade = grd_id
ORDER BY emp_id
Here the following is the query plan of this SQL, it takes 15.92 seconds to finish. The first step of the query plan is ‘SELECT STATEMENT REMOTE’, it means the entire query will be execute on the remote database @richdb and the result will be sent back to the local database. The query plan is a little bit complicated and not easy to tell it is optimal or not. But one thing we can try if the query is partially executed in the local database @local.
In order to request that Oracle perform certain join operations in the local database, the SQL query must include at least one table that is executed in the local database. This allows the use of the hint /*+ DRIVING_SITE ( [ @ queryblock ] tablespec ) */ in the SQL query. If no tables are explicitly executed in the local database, there is no means to request that Oracle attempt to perform join operations in the local database.
Let’s added a dummy condition “EXISTS (SELECT ‘X’ FROM DUAL)” and a hints /*+ DRIVING_SITE(DUAL) */ to the SQL to force Oracle to execute some join operations in the local database.
SELECT /*+ DRIVING_SITE(DUAL) */ *
FROM emp_subsidiary@richdb a,
department@richdb,
grade@richdb
WHERE emp_grade < 1200
AND emp_dept = dpt_id
AND emp_grade = grd_id AND EXISTS ( SELECT ‘x’ FROM dual)
ORDER BY emp_id
Below is the query plan for the modified SQL, which takes 4.08 seconds and is approximately 4 times faster than the original SQL statement where only one join operation is performed in the remote database.
Adding an ORDERED hint to the SQL query can result in further optimization. This will break down the compound statement highlighted in the previous query plan into individual table data remote extraction, as shown in the following query plan.
SELECT /*+ DRIVING_SITE(DUAL) ORDERED */ *
FROM emp_subsidiary@richdb a,
department@richdb,
grade@richdb
WHERE emp_grade < 1200
AND emp_dept = dpt_id
AND emp_grade = grd_id
AND EXISTS ( SELECT ‘x’
FROM dual)
ORDER BY emp_id
If you are familiar with Oracle Exadata, you may notice that the data retrieval process for REMOTE tables in remote database @richdb works similarly to that of the Exadata Storage Server.
It is important to remember that applying this technique to SQL queries with a DB Link is only beneficial in certain environments. For instance, it is ideal when the network speed is good, data traffic is not heavy, and the workload on the local database is low.
Tosska DB Ace for Oracle can automatically perform this type of rewrite, resulting in an SQL query that runs almost 10 times faster than the original.
Here is an example SQL that retrieves data from EMPLOYEE and DEPARTMENT table with the employee’s grade code in the GRADE table.
SELECT emp_id,
emp_name,
dpt_name
FROM employee,
department
WHERE emp_dept = dpt_id
AND emp_grade IN (SELECT grd_id
FROM grade
WHERE grd_min_salary < 200000)
and emp_dept < ‘D’
Here the following is the query plan of this SQL, it takes 8.3 seconds to finish. The query plan shows a Hash Join with GRADE and EMPLOYEE and then hash join to DEPARTMENT. It looks like Oracle gave up any Nested Loops operations after the actual number of rows is returned from the GRADE table in this adaptive plan.
In order to ask Oracle to consider the Nested Loops operations, I added an extra Intersect operation in the subquery to rapidly narrow down the result set of grd_id returned from the GRADE table first.
SELECT emp_id,
emp_name,
dpt_name
FROM employee,
department
WHERE emp_dept = dpt_id
AND emp_grade IN (SELECT grd_id
FROM grade
WHERE grd_min_salary < 200000
INTERSECT SELECT e1.emp_grade
FROM employee e1
WHERE emp_dept < ‘D’)
AND emp_dept < ‘D’
The rewritten SQL generates a query plan that is entirely different from the original query plan, The new plan is using “Nested Loops” from DEPARTMENT to EMPLOYEE as the first steps and then Hash Join to the GRADE table. The new plan now takes 0.81 seconds only.
This kind of rewrite can be achieved by Tosska SQL Tuning Expert Pro for Oracle automatically, it shows that the rewrite is more than 10 times faster than the original SQL.
Some business requirements may need to compare the lower case of an indexed column to a given string as a data retrieval criterion.
Here is an example SQL that retrieves records from the EMPLOYEE table employee if the lower case of the name is equal to the string ‘richard’.
select *
from employee
where LCASE(emp_name)=‘richard’
Here the following are the query plans of this SQL, it takes 17 seconds to finish. The query shows a “Full Table Scan Employee”
You can see that this SQL cannot utilize index scan even if the emp_name is an indexed field. Let me add a “Force Index(emp_name_inx)“hint 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_name >= ””, it is an always true condition that emp_name should be greater or equal to a smallest empty character, it is used to increase the cost of not using emp_name_inx index. There is another condition added “emp_name is null” to correct this condition if emp_name is a null value.
select *
from employee force index(EMPS_NAME_INX)
where LCASE(emp_name) = ‘richard’
and ( emp_name >= ”
or emp_name is null )
Here is the query plan of the rewritten SQL and it is running much faster. The new query plan shows that an Index Scan is used now and takes 2.79 seconds only.
This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 6 times faster than the original SQL.
