At Percona, we constantly search for ways to make query processing more performant. Our activities include continuous monitoring of Percona Server for MySQL performance by doing performance regression tests. We also challenge Percona Server for MySQL with newly designed tests and analyze bottlenecks for possible improvements.
Among our activities in this area is monitoring what is happening in the open source world. We analyze test results, ideas, and patches and decide if they could be applied to Percona Server for MySQL to improve it for our community.
A notable outcome of this research is included in Percona Server for MySQL 8.0.42 and 8.4.5. It is inspired by the work done by Enchanced for MySQL. It improves query processing time by fixing how the range optimizer chooses table scan boundaries.
Let’s consider the following tables:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE t1 ( a INTEGER, b INTEGER, PRIMARY KEY (a) ); CREATE TABLE t2 ( a INTEGER, b INTEGER, PRIMARY KEY (a, b) ); INSERT INTO t1 VALUES (1, 1), (2, 2); INSERT INTO t2 VALUES (2, 1), (2, 2), (2, 3), (2, 4), (2, 5), …, (2, 100000000); |
(t1 has 2 rows, t2 has 100 million rows).
Now, let’s consider the following SELECT query:
|
1 |
SELECT t1.b,t2.b FROM t1 JOIN t2 WHERE t1.a = t2.a AND t2.a = 2 AND t2.b >= t1.b AND t2.b <= t1.b+2; |
and check how the query is executed:
|
1 2 3 4 |
mysql> EXPLAIN FORMAT = TREE SELECT t1.b,t2.b FROM t1 JOIN t2 WHERE t1.a = t2.a AND t2.a = 2 AND t2.b >= t1.b AND t2.b <= t1.b+2G *************************** 1. row *************************** EXPLAIN: -> Filter: ((t2.a = 2) and (t2.b >= '2') and (t2.b <= <cache>(('2' + 2)))) (cost=10e+6 rows=49.9e+6) -> Covering index range scan on t2 using PRIMARY over (a = 2 AND 2 <= b) (cost=10e+6 rows=49.9e+6) |
We see that almost all of table t2 was scanned (all rows where b >=2). Is it necessary?
If we look closer at the query, we see that:
- We are interested only in rows where t2.a = 2
- We are interested only in rows where t1.a = t2.a, in other words, where t1.a = 2. As ‘a’ is the primary key of t1, there is only one row in t1, (2, 2). In other words, t1 can be considered a const table in our query.
- we are interested only in t2 rows where b >= t1.b, in other words b >= 2
- we are interested only in t2 rows where b <= t1.b + 2, in other words b <= 4
Returning to the EXPLAIN result, we see that condition D was ignored during the scan of table t2. It should stop at b = 4, but continues until the end of the table.
The above behavior is caused by the rework done in MySQL in commit 9a13c1c. Initially, the test_quick_select() function dealt with const tables internally, but after the rework, they must be passed as a parameter. The get_quick_record_count() caller passes nullptr, so the information about the const table is lost.
Const tables list is available at the caller level (get_quick_record_count()). The solution is to pass it to test_quick_select().
Let’s check how the query is executed after this fix
|
1 2 3 4 |
mysql> EXPLAIN FORMAT = TREE SELECT t1.b,t2.b FROM t1 JOIN t2 WHERE t1.a = t2.a AND t2.a = 2 AND t2.b >= t1.b AND t2.b <= t1.b+2G *************************** 1. row *************************** EXPLAIN: -> Filter: ((t2.a = 2) and (t2.b >= '2') and (t2.b <= <cache>(('2' + 2)))) (cost=1.33 rows=3) -> Covering index range scan on t2 using PRIMARY over (a = 2 AND 2 <= b <= 4) (cost=1.33 rows=3) |
Now the t2 scan stops at b=4, only 3 rows were scanned.
Let’s see what the performance impact of the fix is.
Without fix:
|
1 2 3 4 5 6 7 8 9 10 |
mysql> SELECT t1.b,t2.b FROM t1 JOIN t2 WHERE t1.a = t2.a AND t2.a = 2 AND t2.b >= t1.b AND t2.b <= t1.b+2; +------+---+ | b | b | +------+---+ | 2 | 2 | | 2 | 3 | | 2 | 4 | +------+---+ 3 rows in set (12,57 sec) |
With fix:
|
1 2 3 4 5 6 7 8 9 |
mysql> SELECT t1.b,t2.b FROM t1 JOIN t2 WHERE t1.a = t2.a AND t2.a = 2 AND t2.b >= t1.b AND t2.b <= t1.b+2; +------+---+ | b | b | +------+---+ | 2 | 2 | | 2 | 3 | | 2 | 4 | +------+---+ 3 rows in set (0,01 sec) |
The performance improvement is significant. The same issue was reported to MySQL, but was not solved. While it applies only to very specific tables and queries, continuously identifying and fixing such corner cases is one of many reasons to choose Percona Server for MySQL over Oracle MySQL.
