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
|
create table t1 (a int primary key, b int, c int, d int, e int, key ba (b,a), key bda (b,d,a), key cba (c,b,a), key cb (c,b), key d (d)) engine=aria;
insert into t1 select seq,seq,seq,seq,seq from seq_1_to_10;
insert into t1 values(20,2,2,2,2),(21,3,4,5,6);
#
# Get different scan costs
#
explain select sum(e) as "table_scan" from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 12
Last_query_cost 0.012556
explain select sum(a) as "index scan" from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 12 Using index
Last_query_cost 0.007441
#
# Range scans should be used if we don't examine all rows in the table
#
explain select count(a) from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Last_query_cost 0.000000
explain select count(*) from t1 where a > 0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 12 Using where; Using index
Last_query_cost 0.002877
explain select count(*) from t1 where a > 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 12 Using where; Using index
Last_query_cost 0.002877
explain select count(*) from t1 where a > 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 11 Using where; Using index
Last_query_cost 0.002747
#
# Shorter indexes are prefered over longer indexs
#
explain select sum(a+b) from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL ba 9 NULL 12 Using index
Last_query_cost 0.007441
explain select count(*) from t1 where b between 5 and 10;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range ba,bda ba 5 NULL 6 Using where; Using index
Last_query_cost 0.002097
explain select sum(b+c) from t1 where b between 5 and 6 and c between 5 and 6;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range ba,bda,cba,cb cba 10 NULL 2 Using where; Using index
Last_query_cost 0.001577
# Cost of 'd' should be slightly smaller as key 'ba' is longer than 'd'
explain select count(*) from t1 where b > 6;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range ba,bda ba 5 NULL 5 Using where; Using index
Last_query_cost 0.001967
explain select count(*) from t1 where d > 6;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range d d 5 NULL 5 Using where; Using index
Last_query_cost 0.001967
#
# Check covering index usage
#
explain select a,b,c from t1 where a=b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL cba 14 NULL 12 Using where; Using index
Last_query_cost 0.007441
#
# Prefer ref keys over ranges
#
explain select count(*) from t1 where b=2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref ba,bda ba 5 const 2 Using index
Last_query_cost 0.001141
explain select count(*) from t1 where b=2 and c=2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref ba,bda,cba,cb cba 10 const,const 2 Using index
Last_query_cost 0.001141
explain select count(*) from t1 where b=3 and c between 3 and 4;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range ba,bda,cba,cb cba 10 NULL 2 Using where; Using index
Last_query_cost 0.001577
#
# Prefer eq keys over ref keys
#
explain select a,b,e from t1 where a=10 or a=11;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition
Last_query_cost 0.003126
explain select a,b,e from t1 where d=10 or d=11;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range d d 5 NULL 2 Using index condition
Last_query_cost 0.003291
drop table t1;
#
# MDEV-30328 Assertion `avg_io_cost != 0.0 || index_cost.io + row_cost.io == 0' failed in
# Cost_estimate::total_cost()
#
set @save=@@InnoDB.optimizer_disk_read_ratio;
set global InnoDB.optimizer_disk_read_ratio=0;
create table t1 (
`l_orderkey` int(11) NOT NULL,
`l_partkey` int(11) DEFAULT NULL,
`l_suppkey` int(11) DEFAULT NULL,
`l_linenumber` int(11) NOT NULL,
`l_extra` int(11) NOT NULL,
`l_quantity` double DEFAULT NULL,
`l_extendedprice` double DEFAULT NULL,
`l_discount` double DEFAULT NULL,
`l_tax` double DEFAULT NULL,
`l_returnflag` char(1) DEFAULT NULL,
`l_linestatus` char(1) DEFAULT NULL,
`l_shipDATE` date DEFAULT NULL,
`l_commitDATE` date DEFAULT NULL,
`l_receiptDATE` date DEFAULT NULL,
`l_shipinstruct` char(25) DEFAULT NULL,
`l_shipmode` char(10) DEFAULT NULL,
`l_comment` varchar(44) DEFAULT NULL,
PRIMARY KEY (`l_orderkey`),
UNIQUE (`l_linenumber`),
UNIQUE (`l_extra`) ,
KEY `l_suppkey` (l_suppkey, l_partkey),
KEY `long_suppkey` (l_partkey, l_suppkey, l_linenumber, l_extra) )
ENGINE= InnoDB;
explain select count(*) from test.t1 force index (l_suppkey) where l_suppkey >= 0 and l_partkey >=0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range l_suppkey l_suppkey 10 NULL 1 Using where; Using index
drop table t1;
set global InnoDB.optimizer_disk_read_ratio=@save;
|
