diff options
author | Monty <monty@mariadb.org> | 2022-06-16 13:12:01 +0300 |
---|---|---|
committer | Sergei Petrunia <sergey@mariadb.com> | 2023-02-02 22:58:38 +0300 |
commit | 4515a89814adaa61b26a900086a54b8cf51f188a (patch) | |
tree | 342a15a1e5b132e651bc24753d32053e9c9da597 /mysql-test/main/order_by.result | |
parent | 1d82e5daf7174a402a603c553357dcb78515eb04 (diff) | |
download | mariadb-git-4515a89814adaa61b26a900086a54b8cf51f188a.tar.gz |
Fixed cost calculations for materialized tables
One effect of this change in the test suite is that tests with very few
rows changed to use sub queries instead of materialization. This is
correct and expected as for these the materialization overhead is too high.
A lot of tests where fixed to still use materialization by adding a
few rows to the tables (most tests has only 2-3 rows and are thus easily
affected when cost computations are changed).
Other things:
- Added more variables to TMPTABLE_COSTS for better cost calculation
- Added cost of copying rows to TMPTABLE_COSTS lookup and write
- Added THD::optimizer_cache_hit_ratio for easier cost calculations
- Added DISK_FAST_READ_SIZE to be used when calculating costs when
reading big blocks from a disk
Diffstat (limited to 'mysql-test/main/order_by.result')
-rw-r--r-- | mysql-test/main/order_by.result | 18 |
1 files changed, 9 insertions, 9 deletions
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index af379cf4aae..95ec2573463 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -4408,7 +4408,7 @@ CREATE TABLE t1 (a INT, b int, primary key(a)); CREATE TABLE t2 (a INT, b INT); INSERT INTO t1 (a,b) VALUES (58,1),(96,2),(273,3),(23,4),(231,5),(525,6), (2354,7),(321421,3),(535,2),(4535,3); -INSERT INTO t2 (a,b) VALUES (58,3),(96,3),(273,3); +INSERT INTO t2 (a,b) VALUES (58,3),(96,3),(273,3),(1000,1000),(2000,2000); # Join order should have the SJM scan table as the first table for both # the queries with GROUP BY and ORDER BY clause. EXPLAIN SELECT t1.a @@ -4416,9 +4416,9 @@ FROM t1 WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) ORDER BY t1.a DESC; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 Using filesort +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using filesort 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using index -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using where EXPLAIN FORMAT=JSON SELECT t1.a FROM t1 WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) @@ -4436,7 +4436,7 @@ EXPLAIN "table_name": "<subquery2>", "access_type": "ALL", "possible_keys": ["distinct_key"], - "rows": 3, + "rows": 5, "filtered": 100, "materialized": { "unique": 1, @@ -4447,7 +4447,7 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", - "rows": 3, + "rows": 5, "filtered": 100, "attached_condition": "t2.b = 3 and t2.a is not null" } @@ -4489,9 +4489,9 @@ FROM t1 WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) GROUP BY t1.a DESC; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 Using filesort +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using filesort 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using where EXPLAIN FORMAT=JSON SELECT t1.a, group_concat(t1.b) FROM t1 WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) @@ -4509,7 +4509,7 @@ EXPLAIN "table_name": "<subquery2>", "access_type": "ALL", "possible_keys": ["distinct_key"], - "rows": 3, + "rows": 5, "filtered": 100, "materialized": { "unique": 1, @@ -4520,7 +4520,7 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", - "rows": 3, + "rows": 5, "filtered": 100, "attached_condition": "t2.b = 3 and t2.a is not null" } |