Debian Codegraph @

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

raw
1 #
2 # Test that histograms are used for optimizing queries that are
3 # offloaded to a secondary storage engine.
4 #
5 CREATE TABLE t1(id INT PRIMARY KEY, x INT NOT NULL) SECONDARY_ENGINE MOCK;
6 INSERT INTO t1 VALUES
7 (1, 1), (2, 1), (3, 1), (4, 1), (5, 1),
8 (6, 1), (7, 1), (8, 1), (9, 2), (10, 2);
9 ALTER TABLE t1 SECONDARY_LOAD;
10 ANALYZE TABLE t1;
11 Table Op Msg_type Msg_text
12 test.t1 analyze status OK
13 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 1;
14 EXPLAIN
15 -> Filter: (t1.x = 1) (rows=1)
16 -> Table scan on t1 in secondary engine MOCK (rows=10)
17
18 Warnings:
19 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
20 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 2;
21 EXPLAIN
22 -> Filter: (t1.x = 2) (rows=1)
23 -> Table scan on t1 in secondary engine MOCK (rows=10)
24
25 Warnings:
26 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
27 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 3;
28 EXPLAIN
29 -> Filter: (t1.x = 3) (rows=1)
30 -> Table scan on t1 in secondary engine MOCK (rows=10)
31
32 Warnings:
33 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
34 ANALYZE TABLE t1 UPDATE HISTOGRAM ON x WITH 10 BUCKETS;
35 Table Op Msg_type Msg_text
36 test.t1 histogram status Histogram statistics created for column 'x'.
37 ANALYZE TABLE t1;
38 Table Op Msg_type Msg_text
39 test.t1 analyze status OK
40 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 1;
41 EXPLAIN
42 -> Filter: (t1.x = 1) (rows=8)
43 -> Table scan on t1 in secondary engine MOCK (rows=10)
44
45 Warnings:
46 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
47 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 2;
48 EXPLAIN
49 -> Filter: (t1.x = 2) (rows=2)
50 -> Table scan on t1 in secondary engine MOCK (rows=10)
51
52 Warnings:
53 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
54 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 3;
55 EXPLAIN
56 -> Filter: (t1.x = 3) (rows=0.01)
57 -> Table scan on t1 in secondary engine MOCK (rows=10)
58
59 Warnings:
60 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
61 SELECT * FROM t1 WHERE x = 1;
62 id x
63 SELECT * FROM t1 WHERE x = 2;
64 id x
65 SELECT * FROM t1 WHERE x = 3;
66 id x
67 ANALYZE TABLE t1 DROP HISTOGRAM ON x;
68 Table Op Msg_type Msg_text
69 test.t1 histogram status Histogram statistics removed for column 'x'.
70 ANALYZE TABLE t1;
71 Table Op Msg_type Msg_text
72 test.t1 analyze status OK
73 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 1;
74 EXPLAIN
75 -> Filter: (t1.x = 1) (rows=1)
76 -> Table scan on t1 in secondary engine MOCK (rows=10)
77
78 Warnings:
79 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
80 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 2;
81 EXPLAIN
82 -> Filter: (t1.x = 2) (rows=1)
83 -> Table scan on t1 in secondary engine MOCK (rows=10)
84
85 Warnings:
86 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
87 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 3;
88 EXPLAIN
89 -> Filter: (t1.x = 3) (rows=1)
90 -> Table scan on t1 in secondary engine MOCK (rows=10)
91
92 Warnings:
93 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
94 ANALYZE TABLE t1 UPDATE HISTOGRAM ON x WITH 10 BUCKETS;
95 Table Op Msg_type Msg_text
96 test.t1 histogram status Histogram statistics created for column 'x'.
97 ANALYZE TABLE t1;
98 Table Op Msg_type Msg_text
99 test.t1 analyze status OK
100 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 1;
101 EXPLAIN
102 -> Filter: (t1.x = 1) (rows=8)
103 -> Table scan on t1 in secondary engine MOCK (rows=10)
104
105 Warnings:
106 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
107 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 2;
108 EXPLAIN
109 -> Filter: (t1.x = 2) (rows=2)
110 -> Table scan on t1 in secondary engine MOCK (rows=10)
111
112 Warnings:
113 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
114 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 3;
115 EXPLAIN
116 -> Filter: (t1.x = 3) (rows=0.01)
117 -> Table scan on t1 in secondary engine MOCK (rows=10)
118
119 Warnings:
120 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
121 DROP TABLE t1;