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