Rewrite Your Complex MySQL Queries for Better Performance
Grøvlen, Øystein
Two MySQL queries that will return the same result, may sometimes have totally different queries plans. This happens because the query optimizer does not realize that the queries are equivalent. In this presentation, we will discuss how we can rewrite queries to help the optimizer find a better query plan. We will show several examples of how we can transform subqueries to make them more efficient, and we will also discuss how we can identify queries that can become faster if a subquery is replaced by window functions. Finally, we will discuss how MySQL 8.0 can do some of these transformations automatically.
 you Hi, my name is Hirsten Graunen and I work for Nalida Babas Polar Levy team, which is based on MySQL. Earlier I worked for 10 years in the MySQL optimizer team at Drogel. My talk today is about how we can help the query optimizer find a better execution plan by slightly rewriting the queries. Most of the rewrites I will discuss is related to subqueries, so before we look at specific ways to rewite queries, I will present some basics about subqueries in SQL. There are basically two types of subqueries, a scalar subquery, which will return at most one row, while a non-scalar subquery may return multiple rows. The examples here show scalar subqueries in the select list and in the where clause, but scalar subqueries may appear almost everywhere a value can be used, like in a group by, order by, having and so on. A non-scalar subquery can replace a table in the front floors, and is then called a derived table. The derived table must be given a name, in this case the name is DT. Non-scalar subqueries can also be used with special conditional operators like in, exists and so on. All the example here shows non-correlated subqueries. They are so called because they do not refer to columns of the outer query. They may be executed independently of the main query, and the result may then be used when executing the outer query. Correlated subqueries contain references to the outer query. Generally, a correlated subquery will have to be re-evaluated for each row generated by the outer query, but as we will see, there are often ways to optimize this. A new type of subquery that has been supported since MySQL 8.0.14 is a lateral derived table. This may refer to preceding tables in the form list. I do not have time to cover these queries in this presentation. Subqueries may be nested. This slide shows a query 20 from the TPCA benchmark. There are two subqueries here nested within an in-subquery. The first one is another in-subquery, while the second one is a correlated scalar subquery. It is correlated because it refers to columns of the part sub table of the outer subquery, and it needs to be scaled in order to be able to compare it with the column value of the outer subquery. As already mentioned, the naive approach for executing subqueries is to evaluate it for each row generated by the outer query. We can speed up execution of correlated subqueries by using indexes. For example, for the query shown here, if there is an index on T2B1, we can use this index for each row in T1. Find all rows that matches A1 and compute the average of the corresponding B2 values. There are several techniques that the optimizer uses to improve this naive approach. Sometimes it is possible to merge the subquery into the outer query. If the subquery is not correlated, we can execute it first and use the resulting value for scalar subqueries. For non-scalar subqueries, the result is stored in a temporary table, and this table is used when executing the outer query. This is called materialization. For in- and exist subqueries, we can use semi-john or anti-john. There is this special type of queries where a value should be greater or smaller than all of the values returned from the subquery. Such queries can be implemented by finding the min or max column value. In this example here, we want to find rows in T1 where A1 is greater than all values of B2. MySQL will then first find the max value for B2 and then use this value when executing the outer query. There are also some new query transformations in A0 that we will get back to later. We will start looking at queries using derived tables, that is, subqueries from close. Traditionally, a derived table has been met materialized, that is, an internal temporary table is created and the result of the subquery is stored in this table. Then the temporary table is used in place of the subquery when executing the main query. The problem with SMI also creates indexes on the temporary table if it finds that to be useful. Since 5.7 a derived table is handled the same way as a view, in fact inline view is another name used for a derived table. This means that a derived table may sometimes not be materialized but merged into the outer query. But there are some restrictions on which derived tables may be merged. Among other things, the subquery must not contain aggregation, limit or union. For example, this query contains a limit clause, so it cannot be merged but will have to be materialized. Note that the subquery will be executed exactly as written, hence when the top query does select star, all columns of the orders table will be materialized. In this case, the other column only needs the total price column, and we see that the execution time is reduced by 2 third if we change the subquery to only select this column. The main saving here is that we reduce the amount of data that needs to be moved around when sorting the many million rows of the orders table. For derived tables that are materialized, you should also make sure to move conditions into the subquery, if possible. In the first example on this slide, we have a condition on a column used in the group by condition, and we have moved that into the very clause of the subquery. Similarly, if there is a condition on some aggregated results from the derived table, it can be moved to the having clause. Note that for newer versions of MySQL, this pushdown will happen automatically. If we look at the output from the new explain format equals 3, we see that while in 8.0.21, the filter is first applied when scanning the materialized table. While in 8.0.22, the filter is applied while scanning the table in a subquery. This way, there will be less data to aggregate and materialize, and the materialized table will be smaller. For this query, with a table of only 1000 rows, doing the earlier filter in saves about 30%. Now, let's take a closer look at what happens when a derived table is merged, and discuss a case where merging is not optimal. We are using this query where we join an ordinary table with a derived table on a column that is not indexed. This query will find two parts with the same name, where one is made of copper, and the other is made of steel. In MySQL 5.5, where merging is not supported, we will join the table with the materialized derived table using block-nested loops joints since we cannot use indexes. This will be pretty slow. In MySQL 5.6, the optimizer will see that it will be useful to create an index on the materialized table, so joining will be fast, and now it only takes 0.4 seconds. So, by using a derived table, the query will be faster than if both tables were joined directly. And many users take advantage of this, and add derived tables in order to get this automatic indexing. However, in MySQL 5.7, merging was introduced, and now we no longer have an index available, and the join will be slower again. In fact, it will be even slower than 5.5 since we will be joining the full two tables instead of the smaller materialized table, with only parts made of steel. So, what can we do to avoid this regression in 5.7? In MySQL 5.7, the only option is to add something to the sub-query so it's not merged. For example, we can add a limit clause with a high enough value to not change the result of the query. In MySQL we have an alternative where we can use a no-merge hint to prevent a derived table from being merged. However, after hash join was introduced in 8.0.18, we do not necessarily need indexes to join efficiently. In fact, for this query, hash join is 35% faster than index nested loop. MySQL 8.0 also introduces common table expression. Instead of using a derived table, we can now declare the sub-query in a width clause before the query, and replace the derived table with the name of the CTE. One advantage is that the readability is better when the main query is not cluttered with multiple sub-queries. Especially in this example, it becomes much clearer that we are using the same derived table twice. Another advantage is that a CTE will only be materialized once, while a derived table will be handled as a separate entity. Hence, using a CTE may give better performance than using a derived table. Here is an example of using a CTE to improve performance. In this case, we use query 15 from TPCH benchmark. The query uses a view, but as I said, a view and a derived table is handled the same way in MySQL. So the same will apply to derived tables as we discussed here. The view computes the revenue made from each supplier, and it uses it twice in the main query, once in the front clause and once in the scalar sub-query in the rear clause, where it finds the supplier with the highest revenue. To use a CTE instead, we just put the view definition in the width clause, and now the view will only be materialized once. Since materialization is the most part of the work in this query, the execution time is cut in half compared to when using a view. So next we will look at how we can improve some queries by rewriting scalar sub-queries. What we will do here is to use a derived table instead of a scalar sub-query. Or in this example I will use a CTE since I find that more readable. The original query, called Q1, uses a scalar sub-query to compute the average quantity that is ordered for some part. And this is used to compute the total price per year for orders of less than 20% of the average quantity. This means that the sub-query of Q1 will compute the average quantity for each part multiple times. So the idea in Q2 is to instead compute the average quantity for each part only once in a CTE or derived table, and then join this with the original query. Then we can do a lookup in this materialized table when running through all the orders. However, note that we are only interested in parts from one specific manufacturer, and Q2 will compute the average quantity for all parts. So the idea in Q3 is that since we do not actually need any information from the part table in the outer query, we can move the part table with the filtering into the CTE. Then we will only compute the average quantity for the interesting parts. So here are the results from running this query. As we can see, we can save significantly by using a derived table instead of a scalar sub-query in this case. And we can even save more if we can put the filtering into the derived table. However, using a derived table will not always speed up the query. Here is a slightly different query where the virtual condition is much more selective. Then computing the average for all parts as done by Q2 will be much slower than the original query. So unless we are able to put filtering into the derived table, we better keep the scalar sub-query in this case. MySQL 8.0.21 supports automatic rewrite for scalar sub-queries to derived tables. But only when the scalar sub-query is not correlated. So it will not have any effect on the query we discussed here. So this feature is also off by default, so you have to set an optimizer switch to turn it on. And there is no cost-based evaluation for this feature, so you may get worse performance with the switch on. So now we move on to sub-queries used by IN or EXIST operations. So traditionally IN sub-queries was often very slow in MySQL, and it gave sub-queries in MySQL a better reputation. However, 5.6 introduced semi-join, which changes this. So the idea is to convert the IN operation to an inner join. However, extra care is needed to remove duplicates. For example, the IN sub-query at the top of this slide finds all orders which included an item that was shipped on a specific date. So we only want to get each order once, even if several items from the same order were shipped at this date. So if we are joining these two tables, we have to remove the duplicate orders. So the advantage of converting to inner join is that now we can use the join optimizer to find the optimal way to access the tables. For the example query with semi-join, we now start with the line-item table and find which items were shipped on this date, and then we look up these orders in the orders table. Without semi-join, we would have to go through all the orders and check if they had any items shipped on this date. So for this particular query, this reduces the execution time from 1 minute to 100 milliseconds. As I mentioned, semi-join can not always be used for IN sub-queries. This query, which is query 18 from the TPCA benchmark, contains a group by so semi-join cannot be used. However, remember that semi-join was needed to remove duplicates. In this case, we know that the sub-query will not produce any duplicates, since we are grouping on the only column on the select list. In that case, we can just use an ordinary join instead. So we put the sub-query in a CTE, and we join it to the rest of the tables using the conditions given by the in-operation. So here we show the two query plans. To the left, we see the original query plan, where the sub-query is materialized. And we do a lookup into the temporary table for each row in the orders table. On the right hand, we see the new plan. Since an ordinary join is now used, the join optimizer may put the materialized table first in the join order, so that we do not need to go through all orders, and the execution time is reduced from 3 to 2 seconds. The last type of sub-query we will look at is how we can use window functions to replace the scale or sub-queries. A few tips each query uses a pattern that we see here for query 17. We use a non-curly sub-query to compute the result, and then the main query will come paired with this result. You have probably already noticed that you have seen this query before. It's the same as we discussed earlier with the scale or sub-queries. So note that we have these queries accessing the line-18 table twice. One is to get the average, and one is to go through and over the items. However, if we use a window function, we can actually go through all items and compute the average at the same time. So by using a window function, and I've shown here in a CTE, we can access the line-18 table only once in this query. Here I show the results for the TPCH queries that may be rewritten using a window function. As you see, the mileage varies. For query 17, the execution time is reduced by 80% from 6.5 to 1.3 seconds. But the savings will depend on how large part of the total work is saved by avoiding accessing the extra table. For query 15, there is no savings since, as discussed earlier, we have already avoided the double table access by using a CTE instead of a Vue. Finally, I will briefly mention that we can rewrite queries by adding optimizer hints. The syntax for optimizer hints is to put the hints in a special comment right after select. The comment must start with a plus sign. There are several hints that affect how mays get executed in subqueries, and we have already discussed the merge or no merge hint. But there are also hints to control which strategy to use for some a join on other in-subqueries. And there are also new hints to control the condition pushdown feature introduced in 8.0.22. There are also other useful hints, for example, to specify the join order or which index to use. But what if you are not able to rewrite your queries because an application cannot be changed? For this purpose, the query-rewrite plugin is provided. So here you can specify how incoming queries should be rewritten by adding a rewrite rule to a specific table. And special APIs is also provided so you can create your own rewrite plugins that intercept and change the query either before or after parsing. So to sum up, I have summarized the main tips on how to re-write your queries to run faster. So you should only select columns in the drive table that will be used by the outer queries, and push condition into your subqueries. And we also show that the automatic merge of the drive tables may not always be optimal, and how common table expression can be used instead of drive tables. We also showed rewriting scalar subqueries to either drive tables or window functions to improve the performance. Another rule, if you are not using a very recent MySQL version, is to prefer in over index, exists. And if SEM Agenda does not apply for your in subqueries, you can check if it can be replaced by a drive table. That's all, and I'm open for questions.
 you  Hi, my name is Hirsten Graunen and I work for Nalida Babas Polar Levy team, which is based on MySQL.  Earlier I worked for 10 years in the MySQL optimizer team at Drogel.  My talk today is about how we can help the query optimizer find a better execution plan by slightly rewriting the queries.  Most of the rewrites I will discuss is related to subqueries, so before we look at specific ways to rewite queries, I will present some basics about subqueries in SQL.  There are basically two types of subqueries, a scalar subquery, which will return at most one row, while a non-scalar subquery may return multiple rows.  The examples here show scalar subqueries in the select list and in the where clause, but scalar subqueries may appear almost everywhere a value can be used, like in a group by, order by, having and so on.  A non-scalar subquery can replace a table in the front floors, and is then called a derived table.  The derived table must be given a name, in this case the name is DT.  Non-scalar subqueries can also be used with special conditional operators like in, exists and so on.  All the example here shows non-correlated subqueries. They are so called because they do not refer to columns of the outer query.  They may be executed independently of the main query, and the result may then be used when executing the outer query.  Correlated subqueries contain references to the outer query. Generally, a correlated subquery will have to be re-evaluated for each row generated by the outer query, but as we will see, there are often ways to optimize this.  A new type of subquery that has been supported since MySQL 8.0.14 is a lateral derived table.  This may refer to preceding tables in the form list. I do not have time to cover these queries in this presentation.  Subqueries may be nested. This slide shows a query 20 from the TPCA benchmark. There are two subqueries here nested within an in-subquery.  The first one is another in-subquery, while the second one is a correlated scalar subquery. It is correlated because it refers to columns of the part sub table of the outer subquery, and it needs to be scaled in order to be able to compare it with the column value of the outer subquery.  As already mentioned, the naive approach for executing subqueries is to evaluate it for each row generated by the outer query.  We can speed up execution of correlated subqueries by using indexes. For example, for the query shown here, if there is an index on T2B1, we can use this index for each row in T1.  Find all rows that matches A1 and compute the average of the corresponding B2 values.  There are several techniques that the optimizer uses to improve this naive approach.  Sometimes it is possible to merge the subquery into the outer query. If the subquery is not correlated, we can execute it first and use the resulting value for scalar subqueries.  For non-scalar subqueries, the result is stored in a temporary table, and this table is used when executing the outer query.  This is called materialization. For in- and exist subqueries, we can use semi-john or anti-john.  There is this special type of queries where a value should be greater or smaller than all of the values returned from the subquery.  Such queries can be implemented by finding the min or max column value.  In this example here, we want to find rows in T1 where A1 is greater than all values of B2.  MySQL will then first find the max value for B2 and then use this value when executing the outer query.  There are also some new query transformations in A0 that we will get back to later.  We will start looking at queries using derived tables, that is, subqueries from close.  Traditionally, a derived table has been met materialized, that is, an internal temporary table is created and the result of the subquery is stored in this table.  Then the temporary table is used in place of the subquery when executing the main query.  The problem with SMI also creates indexes on the temporary table if it finds that to be useful.  Since 5.7 a derived table is handled the same way as a view, in fact inline view is another name used for a derived table.  This means that a derived table may sometimes not be materialized but merged into the outer query.  But there are some restrictions on which derived tables may be merged.  Among other things, the subquery must not contain aggregation, limit or union.  For example, this query contains a limit clause, so it cannot be merged but will have to be materialized.  Note that the subquery will be executed exactly as written, hence when the top query does select star, all columns of the orders table will be materialized.  In this case, the other column only needs the total price column, and we see that the execution time is reduced by 2 third if we change the subquery to only select this column.  The main saving here is that we reduce the amount of data that needs to be moved around when sorting the many million rows of the orders table.  For derived tables that are materialized, you should also make sure to move conditions into the subquery, if possible.  In the first example on this slide, we have a condition on a column used in the group by condition, and we have moved that into the very clause of the subquery.  Similarly, if there is a condition on some aggregated results from the derived table, it can be moved to the having clause.  Note that for newer versions of MySQL, this pushdown will happen automatically.  If we look at the output from the new explain format equals 3, we see that while in 8.0.21, the filter is first applied when scanning the materialized table.  While in 8.0.22, the filter is applied while scanning the table in a subquery.  This way, there will be less data to aggregate and materialize, and the materialized table will be smaller.  For this query, with a table of only 1000 rows, doing the earlier filter in saves about 30%.  Now, let's take a closer look at what happens when a derived table is merged, and discuss a case where merging is not optimal.  We are using this query where we join an ordinary table with a derived table on a column that is not indexed.  This query will find two parts with the same name, where one is made of copper, and the other is made of steel.  In MySQL 5.5, where merging is not supported, we will join the table with the materialized derived table using block-nested loops joints since we cannot use indexes.  This will be pretty slow.  In MySQL 5.6, the optimizer will see that it will be useful to create an index on the materialized table, so joining will be fast, and now it only takes 0.4 seconds.  So, by using a derived table, the query will be faster than if both tables were joined directly.  And many users take advantage of this, and add derived tables in order to get this automatic indexing.  However, in MySQL 5.7, merging was introduced, and now we no longer have an index available, and the join will be slower again.  In fact, it will be even slower than 5.5 since we will be joining the full two tables instead of the smaller materialized table, with only parts made of steel.  So, what can we do to avoid this regression in 5.7?  In MySQL 5.7, the only option is to add something to the sub-query so it's not merged.  For example, we can add a limit clause with a high enough value to not change the result of the query.  In MySQL we have an alternative where we can use a no-merge hint to prevent a derived table from being merged.  However, after hash join was introduced in 8.0.18, we do not necessarily need indexes to join efficiently.  In fact, for this query, hash join is 35% faster than index nested loop.  MySQL 8.0 also introduces common table expression.  Instead of using a derived table, we can now declare the sub-query in a width clause before the query, and replace the derived table with the name of the CTE.  One advantage is that the readability is better when the main query is not cluttered with multiple sub-queries.  Especially in this example, it becomes much clearer that we are using the same derived table twice.  Another advantage is that a CTE will only be materialized once, while a derived table will be handled as a separate entity.  Hence, using a CTE may give better performance than using a derived table.  Here is an example of using a CTE to improve performance.  In this case, we use query 15 from TPCH benchmark.  The query uses a view, but as I said, a view and a derived table is handled the same way in MySQL.  So the same will apply to derived tables as we discussed here.  The view computes the revenue made from each supplier, and it uses it twice in the main query, once in the front clause and once in the scalar sub-query in the rear clause, where it finds the supplier with the highest revenue.  To use a CTE instead, we just put the view definition in the width clause, and now the view will only be materialized once.  Since materialization is the most part of the work in this query, the execution time is cut in half compared to when using a view.  So next we will look at how we can improve some queries by rewriting scalar sub-queries.  What we will do here is to use a derived table instead of a scalar sub-query.  Or in this example I will use a CTE since I find that more readable.  The original query, called Q1, uses a scalar sub-query to compute the average quantity that is ordered for some part.  And this is used to compute the total price per year for orders of less than 20% of the average quantity.  This means that the sub-query of Q1 will compute the average quantity for each part multiple times.  So the idea in Q2 is to instead compute the average quantity for each part only once in a CTE or derived table, and then join this with the original query.  Then we can do a lookup in this materialized table when running through all the orders.  However, note that we are only interested in parts from one specific manufacturer, and Q2 will compute the average quantity for all parts.  So the idea in Q3 is that since we do not actually need any information from the part table in the outer query, we can move the part table with the filtering into the CTE.  Then we will only compute the average quantity for the interesting parts.  So here are the results from running this query.  As we can see, we can save significantly by using a derived table instead of a scalar sub-query in this case.  And we can even save more if we can put the filtering into the derived table.  However, using a derived table will not always speed up the query.  Here is a slightly different query where the virtual condition is much more selective.  Then computing the average for all parts as done by Q2 will be much slower than the original query.  So unless we are able to put filtering into the derived table, we better keep the scalar sub-query in this case.  MySQL 8.0.21 supports automatic rewrite for scalar sub-queries to derived tables.  But only when the scalar sub-query is not correlated.  So it will not have any effect on the query we discussed here.  So this feature is also off by default, so you have to set an optimizer switch to turn it on.  And there is no cost-based evaluation for this feature, so you may get worse performance with the switch on.  So now we move on to sub-queries used by IN or EXIST operations.  So traditionally IN sub-queries was often very slow in MySQL, and it gave sub-queries in MySQL a better reputation.  However, 5.6 introduced semi-join, which changes this.  So the idea is to convert the IN operation to an inner join.  However, extra care is needed to remove duplicates.  For example, the IN sub-query at the top of this slide finds all orders which included an item that was shipped on a specific date.  So we only want to get each order once, even if several items from the same order were shipped at this date.  So if we are joining these two tables, we have to remove the duplicate orders.  So the advantage of converting to inner join is that now we can use the join optimizer to find the optimal way to access the tables.  For the example query with semi-join, we now start with the line-item table and find which items were shipped on this date, and then we look up these orders in the orders table.  Without semi-join, we would have to go through all the orders and check if they had any items shipped on this date.  So for this particular query, this reduces the execution time from 1 minute to 100 milliseconds.  As I mentioned, semi-join can not always be used for IN sub-queries.  This query, which is query 18 from the TPCA benchmark, contains a group by so semi-join cannot be used.  However, remember that semi-join was needed to remove duplicates.  In this case, we know that the sub-query will not produce any duplicates, since we are grouping on the only column on the select list.  In that case, we can just use an ordinary join instead.  So we put the sub-query in a CTE, and we join it to the rest of the tables using the conditions given by the in-operation.  So here we show the two query plans.  To the left, we see the original query plan, where the sub-query is materialized.  And we do a lookup into the temporary table for each row in the orders table.  On the right hand, we see the new plan.  Since an ordinary join is now used, the join optimizer may put the materialized table first in the join order,  so that we do not need to go through all orders, and the execution time is reduced from 3 to 2 seconds.  The last type of sub-query we will look at is how we can use window functions to replace the scale or sub-queries.  A few tips each query uses a pattern that we see here for query 17.  We use a non-curly sub-query to compute the result, and then the main query will come paired with this result.  You have probably already noticed that you have seen this query before.  It's the same as we discussed earlier with the scale or sub-queries.  So note that we have these queries accessing the line-18 table twice.  One is to get the average, and one is to go through and over the items.  However, if we use a window function, we can actually go through all items and compute the average at the same time.  So by using a window function, and I've shown here in a CTE, we can access the line-18 table only once in this query.  Here I show the results for the TPCH queries that may be rewritten using a window function.  As you see, the mileage varies.  For query 17, the execution time is reduced by 80% from 6.5 to 1.3 seconds.  But the savings will depend on how large part of the total work is saved by avoiding accessing the extra table.  For query 15, there is no savings since, as discussed earlier, we have already avoided the double table access by using a CTE instead of a Vue.  Finally, I will briefly mention that we can rewrite queries by adding optimizer hints.  The syntax for optimizer hints is to put the hints in a special comment right after select.  The comment must start with a plus sign.  There are several hints that affect how mays get executed in subqueries, and we have already discussed the merge or no merge hint.  But there are also hints to control which strategy to use for some a join on other in-subqueries.  And there are also new hints to control the condition pushdown feature introduced in 8.0.22.  There are also other useful hints, for example, to specify the join order or which index to use.  But what if you are not able to rewrite your queries because an application cannot be changed?  For this purpose, the query-rewrite plugin is provided.  So here you can specify how incoming queries should be rewritten by adding a rewrite rule to a specific table.  And special APIs is also provided so you can create your own rewrite plugins that intercept and change the query either before or after parsing.  So to sum up, I have summarized the main tips on how to re-write your queries to run faster.  So you should only select columns in the drive table that will be used by the outer queries, and push condition into your subqueries.  And we also show that the automatic merge of the drive tables may not always be optimal, and how common table expression can be used instead of drive tables.  We also showed rewriting scalar subqueries to either drive tables or window functions to improve the performance.  Another rule, if you are not using a very recent MySQL version, is to prefer in over index, exists.  And if SEM Agenda does not apply for your in subqueries, you can check if it can be replaced by a drive table.  That's all, and I'm open for questions.
rewrite_mysql_0_203.png rewrite_mysql_204_959.png rewrite_mysql_960_1643.png rewrite_mysql_1644_2015.png rewrite_mysql_2016_3767.png rewrite_mysql_3768_4811.png rewrite_mysql_4812_5807.png rewrite_mysql_5808_8639.png rewrite_mysql_8640_8867.png rewrite_mysql_8868_10355.png rewrite_mysql_10356_11627.png rewrite_mysql_11628_12743.png rewrite_mysql_12744_13679.png rewrite_mysql_13680_16151.png rewrite_mysql_16152_17219.png rewrite_mysql_17220_18491.png rewrite_mysql_18492_20135.png rewrite_mysql_20136_20387.png rewrite_mysql_20388_22679.png rewrite_mysql_22680_22727.png rewrite_mysql_22728_23219.png rewrite_mysql_23220_24047.png rewrite_mysql_24048_24851.png rewrite_mysql_24852_25031.png rewrite_mysql_25032_27335.png rewrite_mysql_27336_28463.png rewrite_mysql_28464_29495.png rewrite_mysql_29496_29759.png rewrite_mysql_29760_29807.png rewrite_mysql_29808_30947.png rewrite_mysql_30948_31727.png rewrite_mysql_31728_31835.png rewrite_mysql_31836_32015.png rewrite_mysql_32016_32975.png rewrite_mysql_32976_33179.png rewrite_mysql_33180_34091.png rewrite_mysql_34092_34235.png rewrite_mysql_34236_35207.png rewrite_mysql_35208_36947.png rewrite_mysql_36948_37079.png rewrite_mysql_37080_37232.png
you Hi, my name is Hirsten Graunen and I work for Nalida Babas Polar Levy team, which is based on MySQL. Earlier I worked for 10 years in the MySQL optimizer team at Drogel. My talk today is about how we can help the query optimizer find a better execution plan by slightly rewriting the queries. Most of the rewrites I will discuss is related to subqueries, so before we look at specific ways to rewite queries, I will present some basics about subqueries in SQL. There are basically two types of subqueries, a scalar subquery, which will return at most one row, while a non-scalar subquery may return multiple rows. The examples here show scalar subqueries in the select list and in the where clause, but scalar subqueries may appear almost everywhere a value can be used, like in a group by, order by, having and so on. A non-scalar subquery can replace a table in the front floors, and is then called a derived table. The derived table must be given a name, in this case the name is DT. Non-scalar subqueries can also be used with special conditional operators like in, exists and so on. All the example here shows non-correlated subqueries. They are so called because they do not refer to columns of the outer query. They may be executed independently of the main query, and the result may then be used when executing the outer query. Correlated subqueries contain references to the outer query. Generally, a correlated subquery will have to be re-evaluated for each row generated by the outer query, but as we will see, there are often ways to optimize this. A new type of subquery that has been supported since MySQL 8.0.14 is a lateral derived table. This may refer to preceding tables in the form list. I do not have time to cover these queries in this presentation. Subqueries may be nested. This slide shows a query 20 from the TPCA benchmark. There are two subqueries here nested within an in-subquery. The first one is another in-subquery, while the second one is a correlated scalar subquery. It is correlated because it refers to columns of the part sub table of the outer subquery, and it needs to be scaled in order to be able to compare it with the column value of the outer subquery. As already mentioned, the naive approach for executing subqueries is to evaluate it for each row generated by the outer query. We can speed up execution of correlated subqueries by using indexes. For example, for the query shown here, if there is an index on T2B1, we can use this index for each row in T1. Find all rows that matches A1 and compute the average of the corresponding B2 values. There are several techniques that the optimizer uses to improve this naive approach. Sometimes it is possible to merge the subquery into the outer query. If the subquery is not correlated, we can execute it first and use the resulting value for scalar subqueries. For non-scalar subqueries, the result is stored in a temporary table, and this table is used when executing the outer query. This is called materialization. For in- and exist subqueries, we can use semi-john or anti-john. There is this special type of queries where a value should be greater or smaller than all of the values returned from the subquery. Such queries can be implemented by finding the min or max column value. In this example here, we want to find rows in T1 where A1 is greater than all values of B2. MySQL will then first find the max value for B2 and then use this value when executing the outer query. There are also some new query transformations in A0 that we will get back to later. We will start looking at queries using derived tables, that is, subqueries from close. Traditionally, a derived table has been met materialized, that is, an internal temporary table is created and the result of the subquery is stored in this table. Then the temporary table is used in place of the subquery when executing the main query. The problem with SMI also creates indexes on the temporary table if it finds that to be useful. Since 5.7 a derived table is handled the same way as a view, in fact inline view is another name used for a derived table. This means that a derived table may sometimes not be materialized but merged into the outer query. But there are some restrictions on which derived tables may be merged. Among other things, the subquery must not contain aggregation, limit or union. For example, this query contains a limit clause, so it cannot be merged but will have to be materialized. Note that the subquery will be executed exactly as written, hence when the top query does select star, all columns of the orders table will be materialized. In this case, the other column only needs the total price column, and we see that the execution time is reduced by 2 third if we change the subquery to only select this column. The main saving here is that we reduce the amount of data that needs to be moved around when sorting the many million rows of the orders table. For derived tables that are materialized, you should also make sure to move conditions into the subquery, if possible. In the first example on this slide, we have a condition on a column used in the group by condition, and we have moved that into the very clause of the subquery. Similarly, if there is a condition on some aggregated results from the derived table, it can be moved to the having clause. Note that for newer versions of MySQL, this pushdown will happen automatically. If we look at the output from the new explain format equals 3, we see that while in 8.0.21, the filter is first applied when scanning the materialized table. While in 8.0.22, the filter is applied while scanning the table in a subquery. This way, there will be less data to aggregate and materialize, and the materialized table will be smaller. For this query, with a table of only 1000 rows, doing the earlier filter in saves about 30%. Now, let's take a closer look at what happens when a derived table is merged, and discuss a case where merging is not optimal. We are using this query where we join an ordinary table with a derived table on a column that is not indexed. This query will find two parts with the same name, where one is made of copper, and the other is made of steel. In MySQL 5.5, where merging is not supported, we will join the table with the materialized derived table using block-nested loops joints since we cannot use indexes. This will be pretty slow. In MySQL 5.6, the optimizer will see that it will be useful to create an index on the materialized table, so joining will be fast, and now it only takes 0.4 seconds. So, by using a derived table, the query will be faster than if both tables were joined directly. And many users take advantage of this, and add derived tables in order to get this automatic indexing. However, in MySQL 5.7, merging was introduced, and now we no longer have an index available, and the join will be slower again. In fact, it will be even slower than 5.5 since we will be joining the full two tables instead of the smaller materialized table, with only parts made of steel. So, what can we do to avoid this regression in 5.7? In MySQL 5.7, the only option is to add something to the sub-query so it's not merged. For example, we can add a limit clause with a high enough value to not change the result of the query. In MySQL we have an alternative where we can use a no-merge hint to prevent a derived table from being merged. However, after hash join was introduced in 8.0.18, we do not necessarily need indexes to join efficiently. In fact, for this query, hash join is 35% faster than index nested loop. MySQL 8.0 also introduces common table expression. Instead of using a derived table, we can now declare the sub-query in a width clause before the query, and replace the derived table with the name of the CTE. One advantage is that the readability is better when the main query is not cluttered with multiple sub-queries. Especially in this example, it becomes much clearer that we are using the same derived table twice. Another advantage is that a CTE will only be materialized once, while a derived table will be handled as a separate entity. Hence, using a CTE may give better performance than using a derived table. Here is an example of using a CTE to improve performance. In this case, we use query 15 from TPCH benchmark. The query uses a view, but as I said, a view and a derived table is handled the same way in MySQL. So the same will apply to derived tables as we discussed here. The view computes the revenue made from each supplier, and it uses it twice in the main query, once in the front clause and once in the scalar sub-query in the rear clause, where it finds the supplier with the highest revenue. To use a CTE instead, we just put the view definition in the width clause, and now the view will only be materialized once. Since materialization is the most part of the work in this query, the execution time is cut in half compared to when using a view. So next we will look at how we can improve some queries by rewriting scalar sub-queries. What we will do here is to use a derived table instead of a scalar sub-query. Or in this example I will use a CTE since I find that more readable. The original query, called Q1, uses a scalar sub-query to compute the average quantity that is ordered for some part. And this is used to compute the total price per year for orders of less than 20% of the average quantity. This means that the sub-query of Q1 will compute the average quantity for each part multiple times. So the idea in Q2 is to instead compute the average quantity for each part only once in a CTE or derived table, and then join this with the original query. Then we can do a lookup in this materialized table when running through all the orders. However, note that we are only interested in parts from one specific manufacturer, and Q2 will compute the average quantity for all parts. So the idea in Q3 is that since we do not actually need any information from the part table in the outer query, we can move the part table with the filtering into the CTE. Then we will only compute the average quantity for the interesting parts. So here are the results from running this query. As we can see, we can save significantly by using a derived table instead of a scalar sub-query in this case. And we can even save more if we can put the filtering into the derived table. However, using a derived table will not always speed up the query. Here is a slightly different query where the virtual condition is much more selective. Then computing the average for all parts as done by Q2 will be much slower than the original query. So unless we are able to put filtering into the derived table, we better keep the scalar sub-query in this case. MySQL 8.0.21 supports automatic rewrite for scalar sub-queries to derived tables. But only when the scalar sub-query is not correlated. So it will not have any effect on the query we discussed here. So this feature is also off by default, so you have to set an optimizer switch to turn it on. And there is no cost-based evaluation for this feature, so you may get worse performance with the switch on. So now we move on to sub-queries used by IN or EXIST operations. So traditionally IN sub-queries was often very slow in MySQL, and it gave sub-queries in MySQL a better reputation. However, 5.6 introduced semi-join, which changes this. So the idea is to convert the IN operation to an inner join. However, extra care is needed to remove duplicates. For example, the IN sub-query at the top of this slide finds all orders which included an item that was shipped on a specific date. So we only want to get each order once, even if several items from the same order were shipped at this date. So if we are joining these two tables, we have to remove the duplicate orders. So the advantage of converting to inner join is that now we can use the join optimizer to find the optimal way to access the tables. For the example query with semi-join, we now start with the line-item table and find which items were shipped on this date, and then we look up these orders in the orders table. Without semi-join, we would have to go through all the orders and check if they had any items shipped on this date. So for this particular query, this reduces the execution time from 1 minute to 100 milliseconds. As I mentioned, semi-join can not always be used for IN sub-queries. This query, which is query 18 from the TPCA benchmark, contains a group by so semi-join cannot be used. However, remember that semi-join was needed to remove duplicates. In this case, we know that the sub-query will not produce any duplicates, since we are grouping on the only column on the select list. In that case, we can just use an ordinary join instead. So we put the sub-query in a CTE, and we join it to the rest of the tables using the conditions given by the in-operation. So here we show the two query plans. To the left, we see the original query plan, where the sub-query is materialized. And we do a lookup into the temporary table for each row in the orders table. On the right hand, we see the new plan. Since an ordinary join is now used, the join optimizer may put the materialized table first in the join order, so that we do not need to go through all orders, and the execution time is reduced from 3 to 2 seconds. The last type of sub-query we will look at is how we can use window functions to replace the scale or sub-queries. A few tips each query uses a pattern that we see here for query 17. We use a non-curly sub-query to compute the result, and then the main query will come paired with this result. You have probably already noticed that you have seen this query before. It's the same as we discussed earlier with the scale or sub-queries. So note that we have these queries accessing the line-18 table twice. One is to get the average, and one is to go through and over the items. However, if we use a window function, we can actually go through all items and compute the average at the same time. So by using a window function, and I've shown here in a CTE, we can access the line-18 table only once in this query. Here I show the results for the TPCH queries that may be rewritten using a window function. As you see, the mileage varies. For query 17, the execution time is reduced by 80% from 6.5 to 1.3 seconds. But the savings will depend on how large part of the total work is saved by avoiding accessing the extra table. For query 15, there is no savings since, as discussed earlier, we have already avoided the double table access by using a CTE instead of a Vue. Finally, I will briefly mention that we can rewrite queries by adding optimizer hints. The syntax for optimizer hints is to put the hints in a special comment right after select. The comment must start with a plus sign. There are several hints that affect how mays get executed in subqueries, and we have already discussed the merge or no merge hint. But there are also hints to control which strategy to use for some a join on other in-subqueries. And there are also new hints to control the condition pushdown feature introduced in 8.0.22. There are also other useful hints, for example, to specify the join order or which index to use. But what if you are not able to rewrite your queries because an application cannot be changed? For this purpose, the query-rewrite plugin is provided. So here you can specify how incoming queries should be rewritten by adding a rewrite rule to a specific table. And special APIs is also provided so you can create your own rewrite plugins that intercept and change the query either before or after parsing. So to sum up, I have summarized the main tips on how to re-write your queries to run faster. So you should only select columns in the drive table that will be used by the outer queries, and push condition into your subqueries. And we also show that the automatic merge of the drive tables may not always be optimal, and how common table expression can be used instead of drive tables. We also showed rewriting scalar subqueries to either drive tables or window functions to improve the performance. Another rule, if you are not using a very recent MySQL version, is to prefer in over index, exists. And if SEM Agenda does not apply for your in subqueries, you can check if it can be replaced by a drive table. That's all, and I'm open for questions.
how we can help the query optimizer find a better have time to cover these queries in this This means that a derived table may sometimes not and we see that the execution time is reduced by change the result of the query. In MySQL we have are using the same derived table twice. Another rewriting scalar sub-queries. What we will do the original query plan, where the sub-query is query 17, the execution time is reduced by 80% re-write your queries to run faster. So you We also showed rewriting scalar subqueries to either drive tables or window functions to does not apply for your in subqueries, you can check if it can be replaced by a drive table.