| 1 |
-- source include/have_ndb.inc |
| 2 |
|
| 3 |
|
| 4 |
# Create table with unique key, ordered index + blob |
| 5 |
|
| 6 |
create table t1 ( |
| 7 |
th int primary key, |
| 8 |
un int, |
| 9 |
de int, |
| 10 |
rb varchar(1000) character set latin1, |
| 11 |
al longtext, |
| 12 |
key(de), |
| 13 |
unique(rb)) |
| 14 |
engine=ndb; |
| 15 |
|
| 16 |
insert into t1 values (1, 1, 1, repeat('O', 1000), repeat('L', 60000)); |
| 17 |
insert into t1 values (2, 2, 2, repeat('W', 999), repeat('B', 60001)); |
| 18 |
insert into t1 values (3, 3, 3, repeat('P', 998), repeat('P', 60002)); |
| 19 |
insert into t1 values (4, 4, 4, repeat('Q', 997), repeat('E', 60003)); |
| 20 |
insert into t1 values (5, 5, 5, repeat('E', 996), repeat('A', 60004)); |
| 21 |
|
| 22 |
# Pk lookup |
| 23 |
select th, un, de, length(rb), length(al) |
| 24 |
from t1 |
| 25 |
where th in (2,4) |
| 26 |
order by th; |
| 27 |
|
| 28 |
# Uk lookup |
| 29 |
select th, un, de, length(rb), length(al) |
| 30 |
from t1 |
| 31 |
where rb |
| 32 |
in (repeat('O', 1000), repeat('Q', 997)) |
| 33 |
order by th; |
| 34 |
|
| 35 |
# Secondary Index scan |
| 36 |
select th, un, de, length(rb), length(al) |
| 37 |
from t1 |
| 38 |
where de between 3 and 5 |
| 39 |
order by th; |
| 40 |
|
| 41 |
# Table scan |
| 42 |
select th, un, de, length(rb), length(al) |
| 43 |
from t1 |
| 44 |
order by th; |
| 45 |
|
| 46 |
# Update via Pk |
| 47 |
update t1 |
| 48 |
set un=6, de=6, rb= repeat('S', 995), al = repeat('O', 60005) |
| 49 |
where th = 1; |
| 50 |
|
| 51 |
# Update via Uk |
| 52 |
update t1 |
| 53 |
set un=7, de=7, al = repeat('F', 60006) |
| 54 |
where rb = repeat('W', 999); |
| 55 |
|
| 56 |
# Update via Index Scan |
| 57 |
update t1 |
| 58 |
set un= un + 5, rb = repeat('U', 1000 - un), al = repeat('U', 60000 + un) |
| 59 |
where de >= 3 and de <= 5; |
| 60 |
|
| 61 |
# Table scan |
| 62 |
select th, un, de, length(rb), length(al) |
| 63 |
from t1 |
| 64 |
order by th; |
| 65 |
|
| 66 |
# Online alter table |
| 67 |
alter table t1 add column extra varchar(2000); |
| 68 |
|
| 69 |
# Table scanning update |
| 70 |
update t1 set extra = repeat(rb, 2); |
| 71 |
|
| 72 |
# Table scan |
| 73 |
select th, un, de, length(rb), length(al), length(extra) |
| 74 |
from t1 |
| 75 |
order by th; |
| 76 |
|
| 77 |
# Table scan with large pushed filter |
| 78 |
select th, un, de, length(rb), length(al), length(extra) |
| 79 |
from t1 |
| 80 |
where extra in ( |
| 81 |
repeat('U', 2000), |
| 82 |
repeat('U', 1998), |
| 83 |
repeat('U', 1996), |
| 84 |
repeat('U', 1994), |
| 85 |
repeat('U', 1992), |
| 86 |
repeat('U', 1990), |
| 87 |
repeat('U', 1988), |
| 88 |
repeat('U', 1986), |
| 89 |
repeat('U', 1984), |
| 90 |
repeat('U', 1982), |
| 91 |
repeat('U', 1980), |
| 92 |
repeat('U', 1978), |
| 93 |
repeat('U', 1976), |
| 94 |
repeat('U', 1974), |
| 95 |
repeat('U', 1972), |
| 96 |
repeat('U', 1970), |
| 97 |
repeat('U', 1968), |
| 98 |
repeat('U', 1966), |
| 99 |
repeat('U', 1964)) |
| 100 |
order by th; |
| 101 |
|
| 102 |
drop table t1; |