Debian Codegraph @

mysql-test/suite/opt_trace/t/eq_range_statistics.test

raw
1
2 # Tests for eq_range_index_dive_limit variable:
3 # test that the number of ranges are counted correctly and
4 # index statistics kicks in when more than
5 # eq_range_index_dive_limit equality ranges are in the
6 # predicate
7
8 --source include/have_optimizer_trace.inc
9 --source include/have_debug.inc
10 --source include/not_hypergraph.inc # Does not output the same optimizer trace.
11
12 SET optimizer_trace_max_mem_size=1048576; # 1MB
13 SET optimizer_trace="enabled=on,one_line=off";
14 SET end_markers_in_json="on";
15
16 SET eq_range_index_dive_limit=default;
17 SELECT @@eq_range_index_dive_limit;
18
19 CREATE TABLE t1 (
20 a INT,
21 b INT,
22 KEY (a,b)
23 );
24
25 INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
26 INSERT INTO t1 VALUES (4,1), (4,2), (4,3);
27 INSERT INTO t1 VALUES (5,1), (5,2), (5,3);
28 --replace_column 7 #
29 SHOW INDEX FROM t1;
30 ANALYZE TABLE t1;
31 --replace_column 7 #
32 SHOW INDEX FROM t1;
33
34 --echo #####
35 --echo # Apply knowledge about the statistics (each index value for
36 --echo # the first key part has an estimate of 2 rows) to ensure that
37 --echo # index statistics kicks in correctly.
38 --echo #####
39
40 --echo # Index dives are done, giving correct estimate of 3 records
41 EXPLAIN SELECT * FROM t1 WHERE a IN (1,2,3);
42
43 SET eq_range_index_dive_limit=3;
44 SELECT @@eq_range_index_dive_limit;
45
46 # Crash server if records_in_range() is called
47 SET SESSION DEBUG="+d,crash_records_in_range";
48 --echo # Index statistics kicks in, giving incorrect estimate of 3x2=6 records
49 EXPLAIN SELECT * FROM t1 WHERE a IN (1,2,3);
50 SELECT * FROM t1 WHERE a IN (1,2,3);
51 SET SESSION DEBUG="-d,crash_records_in_range";
52
53 --echo #####
54 --echo # Below: A number of tests to verify that the number of equality ranges
55 --echo # are counted correctly
56 --echo #####
57
58 # The limit is 3
59
60 --echo
61 --echo # 2 equality ranges: should not use index statistics
62 EXPLAIN SELECT * FROM t1 WHERE a=5 OR a>10 OR a IN (1);
63 SELECT * FROM information_schema.OPTIMIZER_TRACE;
64
65 # This query will use index statistics (as shown in trace) but
66 # we cannot make it crash if records_in_range() is called
67 # because the "a>10" range will correctly call records_in_range()
68 --echo
69 --echo # 3 equality ranges: should use index statistics
70 EXPLAIN SELECT * FROM t1 WHERE a=5 OR a>10 OR a IN (1,2);
71 SELECT * FROM information_schema.OPTIMIZER_TRACE;
72
73 # Crash server if records_in_range() is called
74 --echo
75 --echo # 3 equality ranges: should use index statistics
76 SET SESSION DEBUG="+d,crash_records_in_range";
77 EXPLAIN SELECT * FROM t1 WHERE a=5 AND (b=2 OR b=3 OR b=4);
78 SELECT * FROM information_schema.OPTIMIZER_TRACE;
79 SET SESSION DEBUG="-d,crash_records_in_range";
80
81 --echo
82 --echo # 2 equality ranges: should not use index statistics
83 EXPLAIN SELECT * FROM t1 WHERE a=5 AND (b=2 OR b=3 OR b>4);
84 SELECT * FROM information_schema.OPTIMIZER_TRACE;
85
86 --echo
87 --echo # 2 equality ranges: should not use index statistics
88 EXPLAIN SELECT * FROM t1 WHERE a=5 AND (b=2 OR b=3 OR b IS NULL);
89 SELECT * FROM information_schema.OPTIMIZER_TRACE;
90
91 --echo
92 --echo # 0 equality ranges: should not use index statistics
93 EXPLAIN SELECT * FROM t1 WHERE a>5 AND (b=2 OR b=3 OR b=4);
94 SELECT * FROM information_schema.OPTIMIZER_TRACE;
95
96 # Turn index statistics off
97 SET eq_range_index_dive_limit=0;
98
99 --echo
100 --echo # 1 equality range: should not use index statistics
101 EXPLAIN SELECT * FROM t1 WHERE a=5;
102 SELECT * FROM information_schema.OPTIMIZER_TRACE;
103
104 DROP TABLE t1;
105
106 SET eq_range_index_dive_limit=default;