Debian Codegraph @

mysql-test/suite/secondary_engine/r/cost_threshold.result

raw
1 #
2 # Test cases for the secondary_engine_cost_threshold variable.
3 #
4 SELECT @@secondary_engine_cost_threshold,
5 @@global.secondary_engine_cost_threshold;
6 @@secondary_engine_cost_threshold @@global.secondary_engine_cost_threshold
7 0.000000 0.000000
8 SET @saved_threshold = @@secondary_engine_cost_threshold;
9 SET @@secondary_engine_cost_threshold = -100;
10 Warnings:
11 Warning 1292 Truncated incorrect secondary_engine_cost_threshold value: '-100'
12 SELECT @@secondary_engine_cost_threshold;
13 @@secondary_engine_cost_threshold
14 0.000000
15 CREATE TABLE t1(x INT, y INT);
16 INSERT INTO t1 VALUES (1, 2), (2, 3), (3, 4);
17 CREATE VIEW v1 AS
18 SELECT t1.x x1, t2.x x2, t3.x x3, t4.x x4, t5.x x5, t6.x x6, t7.x x7,
19 t8.x x8, t9.x x9, t10.x x10, t11.x x11, t12.x x12, t13.x x13
20 FROM t1, t1 t2, t1 t3, t1 t4, t1 t5, t1 t6, t1 t7, t1 t8, t1 t9,
21 t1 t10, t1 t11, t1 t12, t1 t13;
22 ANALYZE TABLE t1;
23 Table Op Msg_type Msg_text
24 test.t1 analyze status OK
25 ALTER TABLE t1 SECONDARY_ENGINE MOCK;
26 ALTER TABLE t1 SECONDARY_LOAD;
27 ANALYZE TABLE t1;
28 Table Op Msg_type Msg_text
29 test.t1 analyze status OK
30 SET @@secondary_engine_cost_threshold = 0;
31 SELECT @@secondary_engine_cost_threshold,
32 @@global.secondary_engine_cost_threshold;
33 @@secondary_engine_cost_threshold @@global.secondary_engine_cost_threshold
34 0.000000 0.000000
35 FLUSH STATUS;
36 SELECT * FROM t1;
37 x y
38 SELECT (SELECT y FROM t1 WHERE x = 1);
39 (SELECT y FROM t1 WHERE x = 1)
40 NULL
41 SELECT * FROM v1;
42 x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13
43 SELECT * FROM t1 WHERE x = (SELECT COUNT(*) FROM v1);
44 x y
45 SELECT * FROM t1 tt WHERE x = (SELECT SUM(x) FROM t1);
46 x y
47 # All five queries are offloaded to the secondary engine.
48 SHOW STATUS LIKE 'Secondary_engine_execution_count';
49 Variable_name Value
50 Secondary_engine_execution_count 5
51 SET @@secondary_engine_cost_threshold = 100;
52 SELECT @@secondary_engine_cost_threshold,
53 @@global.secondary_engine_cost_threshold;
54 @@secondary_engine_cost_threshold @@global.secondary_engine_cost_threshold
55 100.000000 0.000000
56 FLUSH STATUS;
57 SELECT * FROM t1;
58 x y
59 1 2
60 2 3
61 3 4
62 SELECT (SELECT y FROM t1 WHERE x = 1);
63 (SELECT y FROM t1 WHERE x = 1)
64 2
65 SELECT * FROM v1;
66 x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13
67 # The first three queries are not offloaded to the secondary engine.
68 SHOW STATUS LIKE 'Secondary_engine_execution_count';
69 Variable_name Value
70 Secondary_engine_execution_count 1
71 SELECT * FROM t1 WHERE x = (SELECT COUNT(*) FROM v1);
72 x y
73 SELECT * FROM t1 tt WHERE x = (SELECT SUM(x) FROM t1);
74 x y
75 # The last two queries are offloaded to the secondary engine.
76 SHOW STATUS LIKE 'Secondary_engine_execution_count';
77 Variable_name Value
78 Secondary_engine_execution_count 2
79 SET @@secondary_engine_cost_threshold = 1000000;
80 SELECT @@secondary_engine_cost_threshold,
81 @@global.secondary_engine_cost_threshold;
82 @@secondary_engine_cost_threshold @@global.secondary_engine_cost_threshold
83 1000000.000000 0.000000
84 FLUSH STATUS;
85 SELECT * FROM t1;
86 SELECT (SELECT y FROM t1 WHERE x = 1);
87 SELECT * FROM v1;
88 SELECT * FROM t1 WHERE x = (SELECT COUNT(*) FROM v1);
89 SELECT * FROM t1 tt WHERE x = (SELECT SUM(x) FROM t1);
90 # All queries are executed in the primary storage engine.
91 SHOW STATUS LIKE 'Secondary_engine_execution_count';
92 Variable_name Value
93 Secondary_engine_execution_count 0
94 # Set the threshold in a hint.
95 FLUSH STATUS;
96 SELECT /*+ SET_VAR(secondary_engine_cost_threshold = 0) */ * FROM t1;
97 x y
98 SHOW STATUS LIKE 'Secondary_engine_execution_count';
99 Variable_name Value
100 Secondary_engine_execution_count 1
101 FLUSH STATUS;
102 SELECT /*+ SET_VAR(secondary_engine_cost_threshold = 100) */ * FROM t1;
103 x y
104 1 2
105 2 3
106 3 4
107 SHOW STATUS LIKE 'Secondary_engine_execution_count';
108 Variable_name Value
109 Secondary_engine_execution_count 0
110 # The use_secondary_engine variable overrides the threshold.
111 FLUSH STATUS;
112 SELECT /*+ SET_VAR(secondary_engine_cost_threshold = 1000000)
113 SET_VAR(use_secondary_engine = FORCED) */
114 * FROM t1;
115 x y
116 SHOW STATUS LIKE 'Secondary_engine_execution_count';
117 Variable_name Value
118 Secondary_engine_execution_count 1
119 FLUSH STATUS;
120 SELECT /*+ SET_VAR(secondary_engine_cost_threshold = 0)
121 SET_VAR(use_secondary_engine = OFF) */
122 * FROM t1;
123 x y
124 1 2
125 2 3
126 3 4
127 SHOW STATUS LIKE 'Secondary_engine_execution_count';
128 Variable_name Value
129 Secondary_engine_execution_count 0
130 # The threshold is evaluated on each execution.
131 FLUSH STATUS;
132 PREPARE ps FROM 'SELECT * FROM t1';
133 SET @@secondary_engine_cost_threshold = 0;
134 EXECUTE ps;
135 x y
136 SHOW STATUS LIKE 'Secondary_engine_execution_count';
137 Variable_name Value
138 Secondary_engine_execution_count 1
139 SET @@secondary_engine_cost_threshold = 100;
140 EXECUTE ps;
141 x y
142 1 2
143 2 3
144 3 4
145 SHOW STATUS LIKE 'Secondary_engine_execution_count';
146 Variable_name Value
147 Secondary_engine_execution_count 1
148 SET @@secondary_engine_cost_threshold = 0;
149 EXECUTE ps;
150 x y
151 SHOW STATUS LIKE 'Secondary_engine_execution_count';
152 Variable_name Value
153 Secondary_engine_execution_count 2
154 SET @@secondary_engine_cost_threshold = 100;
155 EXECUTE ps;
156 x y
157 1 2
158 2 3
159 3 4
160 SHOW STATUS LIKE 'Secondary_engine_execution_count';
161 Variable_name Value
162 Secondary_engine_execution_count 2
163 DROP PREPARE ps;
164 DROP TABLE t1;
165 DROP VIEW v1;
166 SET @@secondary_engine_cost_threshold = @saved_threshold;
167 SELECT @@secondary_engine_cost_threshold,
168 @@global.secondary_engine_cost_threshold;
169 @@secondary_engine_cost_threshold @@global.secondary_engine_cost_threshold
170 0.000000 0.000000
171 UNINSTALL PLUGIN mock;