Debian Codegraph @

mysql-test/suite/secondary_engine/t/index_statistics.test

raw
1 # Test that a secondary engine can use statistics from indexes in the
2 # primary engine.
3 --source include/elide_costs.inc
4
5 --disable_query_log
6 eval INSTALL PLUGIN mock SONAME '$MOCK_PLUGIN';
7 --enable_query_log
8
9 CREATE TABLE t1(id INT PRIMARY KEY, x INT, y INT, KEY (x))
10 SECONDARY_ENGINE MOCK;
11 INSERT INTO t1 VALUES
12 (1, 1, 1),
13 (2, 1, 1),
14 (3, 1, 1),
15 (4, 1, 1),
16 (5, 1, 1),
17 (6, 1, 1),
18 (7, 1, 1),
19 (8, 2, 2),
20 (9, 2, 2),
21 (10, 3, 3);
22 ALTER TABLE t1 SECONDARY_LOAD;
23 ANALYZE TABLE t1;
24
25 --echo # Uses the primary index to determine 10% filtering effect (one row).
26 --replace_regex $elide_costs
27 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id = 1;
28 --echo # Uses the secondary index to determine 70% filtering effect (matches
29 --echo # rows 1 to 7).
30 --replace_regex $elide_costs
31 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 1;
32 --echo # Uses the secondary index to determine 20% filtering effect (matches
33 --echo # rows 8 and 9).
34 --replace_regex $elide_costs
35 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 2;
36 --echo # Uses the secondary index to determine 10% filtering effect (matches
37 --echo # row 10).
38 --replace_regex $elide_costs
39 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 3;
40 --echo # No index on y, so a heuristic is used for the filtering effect.
41 --replace_regex $elide_costs
42 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE y = 1;
43
44 --echo # Ignoring indexes cause heuristics to be used.
45 --replace_regex $elide_costs
46 EXPLAIN FORMAT=TREE SELECT * FROM t1 USE INDEX () WHERE id = 1;
47 --replace_regex $elide_costs
48 EXPLAIN FORMAT=TREE SELECT * FROM t1 USE INDEX () WHERE x = 1;
49 --replace_regex $elide_costs
50 EXPLAIN FORMAT=TREE SELECT * FROM t1 IGNORE INDEX (PRIMARY) WHERE id = 1;
51 --replace_regex $elide_costs
52 EXPLAIN FORMAT=TREE SELECT * FROM t1 IGNORE INDEX (x) WHERE x = 1;
53
54 --echo # No matching rows. Rounds up estimates to one matching row (10%).
55 --replace_regex $elide_costs
56 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id = 100;
57 --replace_regex $elide_costs
58 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 100;
59
60 --echo # Matches two rows in the primary index (20%).
61 --replace_regex $elide_costs
62 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id = 3 OR id = 7;
63
64 --echo # Matches three rows in the secondary index (30%).
65 --replace_regex $elide_costs
66 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 2 OR x = 3;
67
68 --echo # Matches all rows in the secondary index (100%).
69 --replace_regex $elide_costs
70 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 1 OR x = 2 OR x = 3;
71
72 --echo # Ranges in the primary index.
73 --replace_regex $elide_costs
74 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id > 0;
75 --replace_regex $elide_costs
76 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id > 1;
77 --replace_regex $elide_costs
78 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id > 3;
79 --replace_regex $elide_costs
80 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id > 9;
81 --replace_regex $elide_costs
82 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id > 10;
83 --replace_regex $elide_costs
84 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id < 1;
85 --replace_regex $elide_costs
86 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id < 3;
87 --replace_regex $elide_costs
88 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id < 10;
89 --replace_regex $elide_costs
90 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id BETWEEN 3 AND 7;
91
92 --echo # Ranges in the secondary index.
93 --replace_regex $elide_costs
94 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x < 1;
95 --replace_regex $elide_costs
96 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x < 2;
97 --replace_regex $elide_costs
98 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x < 3;
99 --replace_regex $elide_costs
100 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x < 4;
101 --replace_regex $elide_costs
102 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x BETWEEN 1 AND 2;
103
104 --echo # Indexes should only be used during optimization. Verify that the
105 --echo # execution plan for this query does not use DYNAMIC RANGE access.
106 --echo # (No occurrence of "Range checked for each record" in the plan.)
107 --skip_if_hypergraph # Different query plan (still does not use any indexes).
108 --replace_regex $elide_costs
109 EXPLAIN FORMAT=TREE SELECT * FROM t1 a, t1 b WHERE a.id < b.id;
110
111 DROP TABLE t1;
112
113 --echo # Getting range statistics from spatial indexes is not supported,
114 --echo # since spatial indexes are not ordered.
115 CREATE TABLE geom(g GEOMETRY NOT NULL SRID 0, SPATIAL INDEX(g))
116 SECONDARY_ENGINE MOCK;
117 INSERT INTO geom VALUES (POINT(1, 1)), (POINT(2, 2)), (POINT(3, 3));
118 ALTER TABLE geom SECONDARY_LOAD;
119 ANALYZE TABLE geom;
120 --replace_regex $elide_costs
121 EXPLAIN FORMAT=TREE SELECT ST_AsText(g) FROM geom
122 WHERE MBRContains(POLYGON(LINESTRING(POINT(0,0), POINT(3,0),
123 POINT(3,3), POINT(0,0))),
124 g);
125 DROP TABLE geom;
126
127 --disable_query_log
128 UNINSTALL PLUGIN mock;
129 --enable_query_log