Debian Codegraph @

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

raw
1 --source include/have_optimizer_trace.inc
2 --source include/not_hypergraph.inc # Does not output the same optimizer trace.
3
4 let $engine=innodb;
5 --source include/skip_scan_data.inc
6
7 # Trace test
8 SET optimizer_switch = 'skip_scan=on';
9 SET optimizer_trace="enabled=on,one_line=off";
10
11 # These queries should not do skip scans.
12
13 EXPLAIN SELECT a, b, c, d FROM t WHERE a = 2 AND d >= 98 AND e = 2;
14 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%query_references_nonkey_column%';
15
16 EXPLAIN SELECT a, b, c, d FROM t WHERE a = 5 OR b = 2 AND d >= 98;
17 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%disjuntive_predicate_present%';
18
19 EXPLAIN SELECT a, b, c, d FROM t WHERE a = 5 OR b = 2 OR d >= 98;
20 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%disjuntive_predicate_present%';
21
22 EXPLAIN SELECT a, b, c, d FROM t WHERE a = 5 OR d >= 98;
23 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%disjuntive_predicate_present%';
24
25 EXPLAIN SELECT a, b, c, d FROM t WHERE ((a = 5 AND b = 2) OR a = 2) and d >= 98;
26 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%keypart_in_disjunctive_query%';
27
28 EXPLAIN SELECT a, b, c, d FROM t WHERE a > 2 AND d >= 98;
29 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%prefix_not_const_equality%';
30
31 EXPLAIN SELECT a, b, c, d FROM t WHERE a = 2 AND (d >= 98 OR d < 2);
32 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%range_predicate_too_complex%';
33
34 EXPLAIN SELECT a, b, c, d FROM t WHERE a = 2 AND b = 2;
35 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%no_range_predicate%';
36
37 EXPLAIN SELECT a, b, c, d FROM t WHERE (a < 1 OR a = 4 OR a = 5) AND b IN (1, 2, 3) AND d >= 98;
38 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%prefix_not_const_equality%';
39
40 # test for EXPLAIN output, query should do skip scan.
41 EXPLAIN SELECT a, b, c, d FROM t WHERE a IN (1, 2, 3, 4, 5) AND b IN (1, 2, 3) AND d >= 98;
42 SELECT * FROM information_schema.optimizer_trace;
43
44 EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c > 4 AND d < 3;
45 SELECT * FROM information_schema.optimizer_trace;
46
47 EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND d < 3;
48 SELECT * FROM information_schema.optimizer_trace;
49
50 ANALYZE TABLE t UPDATE HISTOGRAM ON d WITH 10 BUCKETS;
51 EXPLAIN SELECT b FROM t WHERE d < 2;
52 SELECT * FROM information_schema.optimizer_trace;
53
54 SET optimizer_trace="enabled=off";
55
56 DROP TABLE t;
57
58 SET optimizer_switch = 'skip_scan=on';
59 SET optimizer_trace="enabled=on,one_line=off";
60
61 CREATE TABLE t1 (f1 VARCHAR(10), f2 CHAR(10), f3 INT, KEY(f1, f2, f3));
62 INSERT INTO t1 VALUES ('a', 'aaaa', 1);
63 # Skip scan can not be used
64 EXPLAIN SELECT /*+ SKIP_SCAN(t1) */ count(*) FROM t1 WHERE f1 BETWEEN 'a' and 'a ' AND f3 > 0;
65 SELECT count(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE WHERE trace LIKE '%prefix_not_const_equality%';
66 DROP TABLE t1;
67
68 SET optimizer_trace="enabled=off";