diff options
author | Vicențiu Ciorbaru <vicentiu@mariadb.org> | 2022-06-30 14:31:54 +0300 |
---|---|---|
committer | Sergei Petrunia <sergey@mariadb.com> | 2023-02-02 23:03:41 +0300 |
commit | 50e9f7aee5fe7b58a2dedf440b3af423268dac83 (patch) | |
tree | 7730cc4c5f5c30216e87fa255f08eabd54e19e14 /mysql-test/main/fetch_first.result | |
parent | 06be2c64bcd59aa268afd7ad0c4befdec56e4ac9 (diff) | |
download | mariadb-git-50e9f7aee5fe7b58a2dedf440b3af423268dac83.tar.gz |
Rewrite cost computation for filesort operations
This is a rework of how filesort calculates costs to allow functions
like test_if_skip_sort_order() to calculate the cost of filesort to
decide between filesort and using a key to resolve ORDER BY.
Changes:
- Split cost calculation of qsort + optional merge sort and priority queue
to dedicated functions.
- Fixed some wrong calculations of cost in old code (use of log() instead
of log2()).
- Added costs realted to fetching the rows if addon fields are not used.
- Updated get_merge_cost() to take into account that we are going to
read data from temporary files in big chuncks (DISK_CHUNCK_SIZE (64K) and
not in IO_SIZE (4K).
- More code documentation including various variables in Sort_param.
One effect of the cost update is that the cost of priority queue
with addon field has decreased slightly and is used in more cases.
When the rowid is large (like with InnoDB where rowid is the priority key),
using addon fields is in many cases preferable.
Reviewer: Monty
Diffstat (limited to 'mysql-test/main/fetch_first.result')
-rw-r--r-- | mysql-test/main/fetch_first.result | 49 |
1 files changed, 46 insertions, 3 deletions
diff --git a/mysql-test/main/fetch_first.result b/mysql-test/main/fetch_first.result index c2aacbf3687..e36f2db4100 100644 --- a/mysql-test/main/fetch_first.result +++ b/mysql-test/main/fetch_first.result @@ -435,14 +435,57 @@ id first_name last_name score 7 Bob Trasc 9 2 John Doe 6 6 John Elton 8.1 +analyze FORMAT=JSON select * from t1 -order by first_name, last_name +order by first_name, last_name, score +offset 2 rows +fetch first 3 rows only; +ANALYZE +{ + "query_optimization": { + "r_total_time_ms": "REPLACED" + }, + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "read_sorted_file": { + "r_rows": 5, + "filesort": { + "sort_key": "t1.first_name, t1.last_name, t1.score", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "r_limit": 5, + "r_used_priority_queue": true, + "r_output_rows": 6, + "r_sort_mode": "sort_key,addon_fields", + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 8, + "r_rows": 8, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + } + ] + } +} +select * from t1 +order by first_name, last_name, score offset 2 rows fetch first 3 rows only; id first_name last_name score 2 John Doe 6 6 John Elton 8.1 -5 John Smith 7 +4 John Smith 6 select * from t1 order by first_name, last_name offset 2 rows @@ -454,7 +497,7 @@ id first_name last_name score 4 John Smith 6 5 John Smith 7 select * from t1 -order by first_name, last_name +order by first_name, last_name, score offset 3 rows fetch first 3 rows only; id first_name last_name score |