diff options
author | Monty <monty@mariadb.org> | 2020-02-28 12:59:30 +0200 |
---|---|---|
committer | Monty <monty@mariadb.org> | 2020-03-27 03:58:32 +0200 |
commit | eb483c5181ab430877c135c16224284cfc517b3d (patch) | |
tree | 05fc0a6583a84b51c84e72b69191974c4d9ca8c8 /mysql-test/main/range.result | |
parent | b3ab3105fdb34dae6c2d4270751bc0694c3d9df8 (diff) | |
download | mariadb-git-eb483c5181ab430877c135c16224284cfc517b3d.tar.gz |
Updated optimizer costs in multi_range_read_info_const() and sql_select.cc
- multi_range_read_info_const now uses the new records_in_range interface
- Added handler::avg_io_cost()
- Don't calculate avg_io_cost() in get_sweep_read_cost if avg_io_cost is
not 1.0. In this case we trust the avg_io_cost() from the handler.
- Changed test_quick_select to use TIME_FOR_COMPARE instead of
TIME_FOR_COMPARE_IDX to align this with the rest of the code.
- Fixed bug when using test_if_cheaper_ordering where we didn't use
keyread if index was changed
- Fixed a bug where we didn't use index only read when using order-by-index
- Added keyread_time() to HEAP.
The default keyread_time() was optimized for blocks and not suitable for
HEAP. The effect was the HEAP prefered table scans over ranges for btree
indexes.
- Fixed get_sweep_read_cost() for HEAP tables
- Ensure that range and ref have same cost for simple ranges
Added a small cost (MULTI_RANGE_READ_SETUP_COST) to ranges to ensure
we favior ref for range for simple queries.
- Fixed that matching_candidates_in_table() uses same number of records
as the rest of the optimizer
- Added avg_io_cost() to JT_EQ_REF cost. This helps calculate the cost for
HEAP and temporary tables better. A few tests changed because of this.
- heap::read_time() and heap::keyread_time() adjusted to not add +1.
This was to ensure that handler::keyread_time() doesn't give
higher cost for heap tables than for normal tables. One effect of
this is that heap and derived tables stored in heap will prefer
key access as this is now regarded as cheap.
- Changed cost for index read in sql_select.cc to match
multi_range_read_info_const(). All index cost calculation is now
done trough one function.
- 'ref' will now use quick_cost for keys if it exists. This is done
so that for '=' ranges, 'ref' is prefered over 'range'.
- scan_time() now takes avg_io_costs() into account
- get_delayed_table_estimates() uses block_size and avg_io_cost()
- Removed default argument to test_if_order_by_key(); simplifies code
Diffstat (limited to 'mysql-test/main/range.result')
-rw-r--r-- | mysql-test/main/range.result | 32 |
1 files changed, 20 insertions, 12 deletions
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index ff162fc8d69..e56fd46b55f 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -252,7 +252,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref x x 5 const 1 Using index explain select count(*) from t1 where x in (1,2,3,4); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index x x 5 NULL 9 Using where; Using index +1 SIMPLE t1 range x x 5 NULL 4 Using where; Using index drop table t1; CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1)); INSERT INTO t1 VALUES (0),(0),(0),(0),(0),(1),(1); @@ -261,12 +261,12 @@ INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2); explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref j1 j1 4 const 1 Using index -1 SIMPLE t1 index i1 i1 4 NULL 7 Using where; Using index; Using join buffer (flat, BNL join) +1 SIMPLE t1 range i1 i1 4 NULL 7 Using where; Using index; Using join buffer (flat, BNL join) explain select * from t1 force index(i1), t2 force index(j1) where (t1.key1 <t2.keya + 1) and t2.keya=3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref j1 j1 4 const 1 Using index -1 SIMPLE t1 index i1 i1 4 NULL 7 Using where; Using index; Using join buffer (flat, BNL join) +1 SIMPLE t1 range i1 i1 4 NULL 7 Using where; Using index; Using join buffer (flat, BNL join) DROP TABLE t1,t2; CREATE TABLE t1 ( a int(11) default NULL, @@ -327,8 +327,8 @@ KEY recount( owner, line ) INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5); SELECT id, columnid, tableid, content, showid, line, ordinal FROM t1 WHERE owner=11 AND ((columnid IN ( 15, 13, 14 ) AND line IN ( 1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 31 )) OR (columnid IN ( 13, 14 ) AND line IN ( 15 ))) LIMIT 0 , 30; id columnid tableid content showid line ordinal -13 13 1 188 1 5 0 15 15 1 188 1 1 0 +13 13 1 188 1 5 0 drop table t1; create table t1 (id int(10) primary key); insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -723,7 +723,7 @@ WHERE v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 6 Using index condition +1 SIMPLE v ALL OXLEFT,OXRIGHT,OXROOTID NULL NULL NULL 12 Using where 1 SIMPLE s ALL OXLEFT NULL NULL NULL 12 Range checked for each record (index map: 0x4) SELECT s.oxid FROM t1 v, t1 s WHERE @@ -1047,10 +1047,10 @@ INSERT INTO `t1` VALUES ,(13,2),(14,2),(15,3),(16,3),(17,3),(18,3),(19,3); explain select * from t1 where a in (3,4) and b in (1,2,3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL # Using where; Using index +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index explain select * from v1 where a in (3,4) and b in (1,2,3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL # Using where; Using index +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index explain select * from t1 where a between 3 and 4 and b between 1 and 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index @@ -1427,7 +1427,7 @@ SELECT * FROM t3 WHERE a < 5 OR a < 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index a a 10 NULL 23 Using where; Using index +1 SIMPLE t3 range a a 5 NULL 9 Using where; Using index DROP TABLE t1, t2, t3; # # Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN @@ -1981,7 +1981,7 @@ insert into t1 values (0,0,0), (2,2,0), (1,1,1), (2,2,1); explain select * from t1 force index (idx) where a >=1 and c <= 1 and a=b and b > 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index idx idx 15 NULL 4 Using where; Using index +1 SIMPLE t1 range idx idx 5 NULL 2 Using where; Using index select * from t1 force index (idx) where a >=1 and c <= 1 and a=b and b > 1; a b c 2 2 0 @@ -2510,7 +2510,7 @@ insert into t2 values explain select * from t1,t2 where a = d and (a,e) in ((3,3),(7,7),(2,2)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx1,idx2 idx1 5 NULL 3 Using index condition; Using where +1 SIMPLE t2 range|filter idx1,idx2 idx1|idx2 5|5 NULL 3 (60%) Using index condition; Using where; Using rowid filter 1 SIMPLE t1 ref idx idx 5 test.t2.d 8 explain format=json select * from t1,t2 where a = d and (a,e) in ((3,3),(7,7),(2,2)); @@ -2525,6 +2525,14 @@ EXPLAIN "key": "idx1", "key_length": "5", "used_key_parts": ["d"], + "rowid_filter": { + "range": { + "key": "idx2", + "used_key_parts": ["e"] + }, + "rows": 12, + "selectivity_pct": 60 + }, "rows": 3, "filtered": 60, "index_condition": "t2.d is not null", @@ -3105,12 +3113,12 @@ insert into t2 select A.a + B.a*10 + C.a*100 from ten A, ten B,ten C where A.a + # expected type=range, rows=1487 , reason=using index dives analyze SELECT * FROM t1 where a in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t1 index a a 5 NULL 2000 2000.00 74.35 59.95 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 1487 1199.00 100.00 100.00 Using where; Using index insert into t2 values (200),(201); # expected type=range, rows=201 , reason=using index statistics analyze SELECT * FROM t1 where a in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,200,201); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t1 index a a 5 NULL 2000 2000.00 10.05 60.05 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 201 1201.00 100.00 100.00 Using where; Using index drop table t1,ten,t2; # # End of 10.2 tests |