ndb_index_ordered.test 3.29 KB
Newer Older
unknown's avatar
unknown committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
-- source include/have_ndb.inc

--disable_warnings
drop table if exists t1;
--enable_warnings

#
# Simple test to show use of ordered indexes 
#

CREATE TABLE t1 (
  a int unsigned NOT NULL PRIMARY KEY,
  b int unsigned not null,
  c int unsigned,
  KEY(b)	
) engine=ndbcluster;

insert t1 values(1, 2, 3), (2,3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
select * from t1 order by b;
select * from t1 where b >= 4 order by b;
select * from t1 where b = 4 order by b;
select * from t1 where b > 4 order by b;
select * from t1 where b < 4 order by b;
select * from t1 where b <= 4 order by b;

#
# Here we should add some "explain select" to verify that the ordered index is 
# used for these queries.
#

#
# Update using ordered index scan
#

unknown's avatar
unknown committed
35
update t1 set c = 3 where b = 3;
unknown's avatar
unknown committed
36 37 38 39 40 41 42 43 44
select * from t1 order by a;
update t1 set c = 10 where b >= 6;
select * from t1 order by a;
update t1 set c = 11 where b < 5;
select * from t1 order by a;
update t1 set c = 12 where b > 0;
select * from t1 order by a;
update t1 set c = 13 where b <= 3;
select * from t1 order by a;
unknown's avatar
unknown committed
45 46
update t1 set b = b + 1 where b > 4 and b < 7;
select * from t1 order by a;
47 48 49
-- Update primary key
update t1 set a = a + 10 where b > 1 and b < 7;
select * from t1 order by a;
unknown's avatar
unknown committed
50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65

#
# Delete using ordered index scan
#

drop table t1;

CREATE TABLE t1 (
  a int unsigned NOT NULL PRIMARY KEY,
  b int unsigned not null,
  c int unsigned,
  KEY(b)	
) engine=ndbcluster;

insert t1 values(1, 2, 13), (2,3, 13), (3, 4, 12), (4, 5, 12), (5,6, 12), (6,7, 12);

unknown's avatar
unknown committed
66
delete from t1 where b = 3;
unknown's avatar
unknown committed
67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85
select * from t1 order by a;
delete from t1 where b >= 6;
select * from t1 order by a;
delete from t1 where b < 4;
select * from t1 order by a;
delete from t1 where b > 5;
select * from t1 order by a;
delete from t1 where b <= 4;
select * from t1 order by a;

drop table t1;


#
#multi part key
#
CREATE TABLE t1 (
  a int unsigned NOT NULL PRIMARY KEY,
  b int unsigned not null,
unknown's avatar
unknown committed
86
  c int unsigned not null
unknown's avatar
unknown committed
87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103
) engine = ndb;

create index a1 on t1 (b, c);

insert into t1 values (1, 2, 13);
insert into t1 values (2,3, 13);
insert into t1 values (3, 4, 12);
insert into t1 values (4, 5, 12);
insert into t1 values (5,6, 12);
insert into t1 values (6,7, 12);
insert into t1 values (7, 2, 1);
insert into t1 values (8,3, 6);
insert into t1 values (9, 4, 12);
insert into t1 values (14, 5, 4);
insert into t1 values (15,5,5);
insert into t1 values (16,5, 6);
insert into t1 values (17,4,4);
unknown's avatar
unknown committed
104 105 106
insert into t1 values (18,1, 7);


unknown's avatar
unknown committed
107 108

select * from t1 order by a;
unknown's avatar
unknown committed
109 110 111 112
select * from t1 where b<=5 order by a;
select * from t1 where b<=5 and c=0;
insert into t1 values (19,4, 0);
select * from t1 where b<=5 and c=0;
113
select * from t1 where b=4 and c<=5 order by a;
unknown's avatar
unknown committed
114 115
select * from t1 where b<=4 and c<=5 order by a;
select * from t1 where b<=5 and c=0 or b<=5 and c=2;
unknown's avatar
unknown committed
116
drop table t1;
117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136

#
# Indexing NULL values
#

#CREATE TABLE t1 (
#  a int unsigned NOT NULL PRIMARY KEY,
#  b int unsigned,
#  c int unsigned,
#  KEY bc(b,c)
#) engine = ndb;

#insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL);
#select * from t1 use index (bc);
#select count(*) from t1 use index (bc);
#select count(*) from t1 use index (PRIMARY) where b IS NULL;
#select count(*) from t1 use index (bc) where b IS NULL;
#select count(*) from t1 use index (bc) where b IS NULL and c = 2;
#select count(*) from t1 use index (bc) where b IS NOT NULL;
#drop table t1;