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
|
# ticket 895 is a query optimization problem with the primary key
--source include/have_tokudb.inc
SET DEFAULT_STORAGE_ENGINE = 'tokudb';
--disable_warnings
DROP TABLE IF EXISTS foo;
--enable_warnings
create table foo (a int, b int, c int, primary key (a,b));
insert into foo values (1,10,100),(2,20,200),(3,30,300),(4,40,400),(5,50,500),(6,60,600);
insert into foo values (1,100,100),(2,200,200),(3,300,300),(4,400,400),(5,500,500),(6,600,600);
select * from foo;
#HA_READ_KEY_EXACT
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a=4;
select * from foo where a=4;
#HA_READ_AFTER_KEY
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a>4;
select * from foo where a>4;
#HA_READ_BEFORE_KEY
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a<3 order by a desc;
select * from foo where a<3 order by a desc;
#HA_READ_KEY_OR_NEXT
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a>=4;
select * from foo where a>=4;
#HA_READ_KEY_OR_PREV not used anymore
#HA_READ_PREFIX_LAST_OR_PREV
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a<=2 order by a desc;
select * from foo where a<=2 order by a desc;
#HA_READ_PREFIX_LAST
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a=4 order by b desc;
select * from foo where a=4 order by b desc;
alter table foo drop primary key;
alter table foo add index clst_a(a,b) clustering=yes;
#HA_READ_KEY_EXACT
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a=4;
select * from foo where a=4;
#HA_READ_AFTER_KEY
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a>4;
select * from foo where a>4;
#HA_READ_BEFORE_KEY
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a<3 order by a desc;
select * from foo where a<3 order by a desc;
#HA_READ_KEY_OR_NEXT
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a>=4;
select * from foo where a>=4;
#HA_READ_KEY_OR_PREV not used anymore
#HA_READ_PREFIX_LAST_OR_PREV
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a<=2 order by a desc;
select * from foo where a<=2 order by a desc;
#HA_READ_PREFIX_LAST
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a=4 order by b desc;
select * from foo where a=4 order by b desc;
alter table foo drop index clst_a;
alter table foo add index (a,b);
#HA_READ_KEY_EXACT
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a=4;
select * from foo where a=4;
#HA_READ_AFTER_KEY
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a>4;
select * from foo where a>4;
#HA_READ_BEFORE_KEY
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a<3 order by a desc;
select * from foo where a<3 order by a desc;
#HA_READ_KEY_OR_NEXT
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a>=4;
select * from foo where a>=4;
#HA_READ_KEY_OR_PREV not used anymore
#HA_READ_PREFIX_LAST_OR_PREV
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a<=2 order by a desc;
select * from foo where a<=2 order by a desc;
#HA_READ_PREFIX_LAST
# ignore rows column
--replace_column 9 NULL;
explain select * from foo where a=4 order by b desc;
select * from foo where a=4 order by b desc;
# Final cleanup.
DROP TABLE foo;
|
