diff options
author | Monty <monty@mariadb.org> | 2022-10-04 16:16:06 +0300 |
---|---|---|
committer | Sergei Petrunia <sergey@mariadb.com> | 2023-02-03 00:00:35 +0300 |
commit | 727491b72adcf3c10b252a61579f70c7ec2246be (patch) | |
tree | b5ac3878d973ac0a01bd86d0c8a4d7d7723ecc03 /mysql-test/suite/funcs_1 | |
parent | eb68023c8e3f014495cd694e1b8d5dafc1e37a8e (diff) | |
download | mariadb-git-727491b72adcf3c10b252a61579f70c7ec2246be.tar.gz |
Added test cases for preceding test
This includes all test changes from
"Changing all cost calculation to be given in milliseconds"
and forwards.
Some of the things that caused changes in the result files:
- As part of fixing tests, I added 'echo' to some comments to be able to
easier find out where things where wrong.
- MATERIALIZED has now a higher cost compared to X than before. Because
of this some MATERIALIZED types have changed to DEPENDEND SUBQUERY.
- Some test cases that required MATERIALIZED to repeat a bug was
changed by adding more rows to force MATERIALIZED to happen.
- 'Filtered' in SHOW EXPLAIN has in many case changed from 100.00 to
something smaller. This is because now filtered also takes into
account the smallest possible ref access and filters, even if they
where not used. Another reason for 'Filtered' being smaller is that
we now also take into account implicit filtering done for subqueries
using FIRSTMATCH.
(main.subselect_no_exists_to_in)
This is caluculated in best_access_path() and stored in records_out.
- Table orders has changed because more accurate costs.
- 'index' and 'ALL' for small tables has changed to use 'range' or
'ref' because of optimizer_scan_setup_cost.
- index can be changed to 'range' as 'range' optimizer assumes we don't
have to read the blocks from disk that range optimizer has already read.
This can be confusing in the case where there is no obvious where clause
but instead there is a hidden 'key_column > NULL' added by the optimizer.
(main.subselect_no_exists_to_in)
- Scan on primary clustered key does not report 'Using Index' anymore
(It's a table scan, not an index scan).
- For derived tables, the number of rows is now 100 instead of 2,
which can be seen in EXPLAIN.
- More tests have "Using index for group by" as the cost of this
optimization is now more correct (lower).
- A primary key could be preferred for a normal key, even if it would
access more rows, as it's faster to do 1 lokoup and 3 'index_next' on a
clustered primary key than one lookup trough a secondary.
(main.stat_tables_innodb)
Notes:
- There was a 4.7% more calls to best_extension_by_limited_search() in
the main.greedy_optimizer test. However examining the test results
it looked that the plans where slightly better (eq_ref where more
chained together) so I assume this is ok.
- I have verified a few test cases where there was notable/unexpected
changes in the plan and in all cases the new optimizer plans where
faster. (main.greedy_optimizer and some others)
Diffstat (limited to 'mysql-test/suite/funcs_1')
-rw-r--r-- | mysql-test/suite/funcs_1/r/is_columns_is.result | 26 | ||||
-rw-r--r-- | mysql-test/suite/funcs_1/r/is_columns_is_embedded.result | 26 | ||||
-rw-r--r-- | mysql-test/suite/funcs_1/r/is_tables_is.result | 50 | ||||
-rw-r--r-- | mysql-test/suite/funcs_1/r/is_tables_is_embedded.result | 50 |
4 files changed, 152 insertions, 0 deletions
diff --git a/mysql-test/suite/funcs_1/r/is_columns_is.result b/mysql-test/suite/funcs_1/r/is_columns_is.result index c88a3a9ac8d..be0d6aa7740 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_is.result +++ b/mysql-test/suite/funcs_1/r/is_columns_is.result @@ -210,6 +210,19 @@ def information_schema KEY_COLUMN_USAGE REFERENCED_TABLE_SCHEMA 10 NULL YES varc def information_schema KEY_COLUMN_USAGE TABLE_CATALOG 4 NULL NO varchar 512 1536 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(512) select NEVER NULL def information_schema KEY_COLUMN_USAGE TABLE_NAME 6 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) select NEVER NULL def information_schema KEY_COLUMN_USAGE TABLE_SCHEMA 5 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) select NEVER NULL +def information_schema OPTIMIZER_COSTS ENGINE 1 NULL NO varchar 192 576 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(192) select NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_DISK_READ_COST 2 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) select NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_DISK_READ_RATIO 8 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) select NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_INDEX_BLOCK_COPY_COST 3 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) select NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_KEY_COMPARE_COST 4 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) select NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_KEY_COPY_COST 5 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) select NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_KEY_LOOKUP_COST 6 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) select NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_KEY_NEXT_FIND_COST 7 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) select NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_ROWID_COMPARE_COST 12 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) select NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_ROWID_COPY_COST 13 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) select NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_ROW_COPY_COST 9 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) select NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_ROW_LOOKUP_COST 10 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) select NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_ROW_NEXT_FIND_COST 11 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) select NEVER NULL def information_schema OPTIMIZER_TRACE INSUFFICIENT_PRIVILEGES 4 NULL NO tinyint NULL NULL 3 0 NULL NULL NULL tinyint(1) select NEVER NULL def information_schema OPTIMIZER_TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE 3 NULL NO int NULL NULL 10 0 NULL NULL NULL int(20) select NEVER NULL def information_schema OPTIMIZER_TRACE QUERY 1 NULL NO longtext 4294967295 4294967295 NULL NULL NULL utf8mb3 utf8mb3_general_ci longtext select NEVER NULL @@ -761,6 +774,19 @@ NULL information_schema KEY_COLUMN_USAGE POSITION_IN_UNIQUE_CONSTRAINT bigint NU 3.0000 information_schema KEY_COLUMN_USAGE REFERENCED_TABLE_SCHEMA varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) 3.0000 information_schema KEY_COLUMN_USAGE REFERENCED_TABLE_NAME varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) 3.0000 information_schema KEY_COLUMN_USAGE REFERENCED_COLUMN_NAME varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) +3.0000 information_schema OPTIMIZER_COSTS ENGINE varchar 192 576 utf8mb3 utf8mb3_general_ci varchar(192) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_DISK_READ_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_INDEX_BLOCK_COPY_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_KEY_COMPARE_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_KEY_COPY_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_KEY_LOOKUP_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_KEY_NEXT_FIND_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_DISK_READ_RATIO decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_ROW_COPY_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_ROW_LOOKUP_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_ROW_NEXT_FIND_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_ROWID_COMPARE_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_ROWID_COPY_COST decimal NULL NULL NULL NULL decimal(9,6) 1.0000 information_schema OPTIMIZER_TRACE QUERY longtext 4294967295 4294967295 utf8mb3 utf8mb3_general_ci longtext 1.0000 information_schema OPTIMIZER_TRACE TRACE longtext 4294967295 4294967295 utf8mb3 utf8mb3_general_ci longtext NULL information_schema OPTIMIZER_TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE int NULL NULL NULL NULL int(20) diff --git a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result index bb12a0c38df..f9906d3f177 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result @@ -210,6 +210,19 @@ def information_schema KEY_COLUMN_USAGE REFERENCED_TABLE_SCHEMA 10 NULL YES varc def information_schema KEY_COLUMN_USAGE TABLE_CATALOG 4 NULL NO varchar 512 1536 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(512) NEVER NULL def information_schema KEY_COLUMN_USAGE TABLE_NAME 6 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) NEVER NULL def information_schema KEY_COLUMN_USAGE TABLE_SCHEMA 5 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) NEVER NULL +def information_schema OPTIMIZER_COSTS ENGINE 1 NULL NO varchar 192 576 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(192) NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_DISK_READ_COST 2 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_DISK_READ_RATIO 8 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_INDEX_BLOCK_COPY_COST 3 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_KEY_COMPARE_COST 4 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_KEY_COPY_COST 5 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_KEY_LOOKUP_COST 6 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_KEY_NEXT_FIND_COST 7 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_ROWID_COMPARE_COST 12 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_ROWID_COPY_COST 13 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_ROW_COPY_COST 9 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_ROW_LOOKUP_COST 10 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) NEVER NULL +def information_schema OPTIMIZER_COSTS OPTIMIZER_ROW_NEXT_FIND_COST 11 NULL NO decimal NULL NULL 9 6 NULL NULL NULL decimal(9,6) NEVER NULL def information_schema OPTIMIZER_TRACE INSUFFICIENT_PRIVILEGES 4 NULL NO tinyint NULL NULL 3 0 NULL NULL NULL tinyint(1) NEVER NULL def information_schema OPTIMIZER_TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE 3 NULL NO int NULL NULL 10 0 NULL NULL NULL int(20) NEVER NULL def information_schema OPTIMIZER_TRACE QUERY 1 NULL NO longtext 4294967295 4294967295 NULL NULL NULL utf8mb3 utf8mb3_general_ci longtext NEVER NULL @@ -761,6 +774,19 @@ NULL information_schema KEY_COLUMN_USAGE POSITION_IN_UNIQUE_CONSTRAINT bigint NU 3.0000 information_schema KEY_COLUMN_USAGE REFERENCED_TABLE_SCHEMA varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) 3.0000 information_schema KEY_COLUMN_USAGE REFERENCED_TABLE_NAME varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) 3.0000 information_schema KEY_COLUMN_USAGE REFERENCED_COLUMN_NAME varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) +3.0000 information_schema OPTIMIZER_COSTS ENGINE varchar 192 576 utf8mb3 utf8mb3_general_ci varchar(192) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_DISK_READ_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_INDEX_BLOCK_COPY_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_KEY_COMPARE_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_KEY_COPY_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_KEY_LOOKUP_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_KEY_NEXT_FIND_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_DISK_READ_RATIO decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_ROW_COPY_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_ROW_LOOKUP_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_ROW_NEXT_FIND_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_ROWID_COMPARE_COST decimal NULL NULL NULL NULL decimal(9,6) +NULL information_schema OPTIMIZER_COSTS OPTIMIZER_ROWID_COPY_COST decimal NULL NULL NULL NULL decimal(9,6) 1.0000 information_schema OPTIMIZER_TRACE QUERY longtext 4294967295 4294967295 utf8mb3 utf8mb3_general_ci longtext 1.0000 information_schema OPTIMIZER_TRACE TRACE longtext 4294967295 4294967295 utf8mb3 utf8mb3_general_ci longtext NULL information_schema OPTIMIZER_TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE int NULL NULL NULL NULL int(20) diff --git a/mysql-test/suite/funcs_1/r/is_tables_is.result b/mysql-test/suite/funcs_1/r/is_tables_is.result index c18f733c86f..93ede4d08cc 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_is.result +++ b/mysql-test/suite/funcs_1/r/is_tables_is.result @@ -514,6 +514,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME OPTIMIZER_COSTS +TABLE_TYPE SYSTEM VIEW +ENGINE MEMORY +VERSION 11 +ROW_FORMAT Fixed +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8mb3_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME OPTIMIZER_TRACE TABLE_TYPE SYSTEM VIEW ENGINE MYISAM_OR_MARIA @@ -1630,6 +1655,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME OPTIMIZER_COSTS +TABLE_TYPE SYSTEM VIEW +ENGINE MEMORY +VERSION 11 +ROW_FORMAT Fixed +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8mb3_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME OPTIMIZER_TRACE TABLE_TYPE SYSTEM VIEW ENGINE MYISAM_OR_MARIA diff --git a/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result b/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result index c18f733c86f..93ede4d08cc 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result @@ -514,6 +514,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME OPTIMIZER_COSTS +TABLE_TYPE SYSTEM VIEW +ENGINE MEMORY +VERSION 11 +ROW_FORMAT Fixed +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8mb3_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME OPTIMIZER_TRACE TABLE_TYPE SYSTEM VIEW ENGINE MYISAM_OR_MARIA @@ -1630,6 +1655,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME OPTIMIZER_COSTS +TABLE_TYPE SYSTEM VIEW +ENGINE MEMORY +VERSION 11 +ROW_FORMAT Fixed +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8mb3_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME OPTIMIZER_TRACE TABLE_TYPE SYSTEM VIEW ENGINE MYISAM_OR_MARIA |