| 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; |