Debian Codegraph @

mysql-test/suite/ndb_opt/statistics.inc

raw
1 -- source include/have_ndb.inc
2
3 CREATE TABLE t10(
4 K INT NOT NULL AUTO_INCREMENT,
5 I INT, J INT,
6 PRIMARY KEY(K),
7 KEY(I,J),
8 UNIQUE KEY(J,K)
9 ) ENGINE=ndbcluster
10 partition by key (K) partitions 1;
11
12 INSERT INTO t10(I,J) VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
13
14 CREATE TABLE t100 LIKE t10;
15 INSERT INTO t100(I,J)
16 SELECT X.J, X.J+(10*Y.J) FROM t10 AS X,t10 AS Y;
17
18 CREATE TABLE t10000 LIKE t10;
19
20 # Insert into t10000 into innodb and alter to ndb
21 # not to exhaust MaxNoOfConcurrentOperations
22 ALTER TABLE t10000 ENGINE=INNODB;
23 INSERT INTO t10000(I,J)
24 SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y;
25 ALTER TABLE t10000 ENGINE=NDBCLUSTER;
26
27 ANALYZE TABLE t10,t100,t10000;
28
29 SELECT COUNT(*) FROM t10;
30 SELECT COUNT(*) FROM t100;
31 SELECT COUNT(*) FROM t10000;
32
33 #
34 # Bug #59517: Incorrect detection of single row access in
35 # ha_ndbcluster::records_in_range()
36
37 # Expect a single row (or const) when PK is excact specified
38 EXPLAIN
39 SELECT * FROM t10000 WHERE k = 42;
40
41 # All queries below should *not* return a single row
42 EXPLAIN
43 SELECT * FROM t10000 WHERE k >= 42 and k < 10000;
44 EXPLAIN
45 SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000;
46 EXPLAIN
47 SELECT * FROM t10000 WHERE k < 42;
48 EXPLAIN
49 SELECT * FROM t10000 WHERE k > 42;
50
51 #
52 # Bug #59519 ::set_rec_per_key() assumes ORDER_INDEX to be unique
53 #
54
55 # 'REF' join of 'Y' should match >1 rows
56 EXPLAIN
57 SELECT * FROM t10000 AS x JOIN t10000 AS y
58 ON y.i=x.i AND y.j = x.i;
59
60 #
61 # Bug #11804277: INCORRECT INDEX MAY BE SELECTED DUE TO INSUFFICIENT
62 # STATISTICS FROM CLUSTER
63 #
64
65 # Open bounded range should return 10% of #rows in table
66 EXPLAIN
67 SELECT * FROM t100 WHERE k < 42;
68 EXPLAIN
69 SELECT * FROM t100 WHERE k > 42;
70 EXPLAIN
71 SELECT * FROM t10000 WHERE k < 42;
72 EXPLAIN
73 SELECT * FROM t10000 WHERE k > 42;
74
75 #Closed bounded range should return 5% of #rows in table
76 EXPLAIN
77 SELECT * FROM t100 WHERE k BETWEEN 42 AND 10000;
78 EXPLAIN
79 SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000;
80
81 #EQ-range selectivity depends on
82 # - key length specified
83 # - #rows in table.
84 # - unique/non-unique index
85 # - min 2% selectivity
86 #
87 # Possibly combined with open/closed ranges as
88 # above which further improves selectivity
89 #
90 EXPLAIN
91 SELECT * FROM t10000 WHERE I = 0;
92 EXPLAIN
93 SELECT * FROM t10000 WHERE J = 0;
94
95 EXPLAIN
96 SELECT * FROM t10000 WHERE I = 0 AND J = 0;
97
98 EXPLAIN
99 SELECT * FROM t10000 WHERE I = 0;
100 EXPLAIN
101 SELECT * FROM t10000 WHERE I = 0 AND J > 1;
102 EXPLAIN
103 SELECT * FROM t10000 WHERE I = 0 AND J < 1;
104 EXPLAIN
105 SELECT * FROM t10000 WHERE I = 0 AND J BETWEEN 1 AND 10;
106 EXPLAIN
107 SELECT * FROM t10000 WHERE I = 0 AND J = 1;
108
109 EXPLAIN
110 SELECT * FROM t10000 WHERE J = 0;
111 EXPLAIN
112 SELECT * FROM t10000 WHERE J = 0 AND K > 1;
113 EXPLAIN
114 SELECT * FROM t10000 WHERE J = 0 AND K < 1;
115 EXPLAIN
116 SELECT * FROM t10000 WHERE J = 0 AND K BETWEEN 1 AND 10;
117 EXPLAIN
118 SELECT * FROM t10000 WHERE J = 0 AND K = 1;
119
120 ## Verify selection of 'best' index
121 ## (The one of index I/J being EQ)
122 EXPLAIN
123 SELECT * FROM t10000 WHERE I = 0 AND J <> 1;
124 EXPLAIN
125 SELECT * FROM t10000 WHERE I <> 0 AND J = 1;
126 EXPLAIN
127 SELECT * FROM t10000 WHERE I <> 0 AND J <> 1;
128
129 EXPLAIN
130 SELECT * FROM t10000 WHERE J <> 1 AND I = 0;
131 EXPLAIN
132 SELECT * FROM t10000 WHERE J = 1 AND I <> 0;
133 EXPLAIN
134 SELECT * FROM t10000 WHERE J <> 1 AND I <> 0;
135
136
137 DROP TABLE t10,t100,t10000;