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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
|
#
# Only run the test if we are using --big-test, because this test takes a
# long time
#
#-- require r/big_test.require
#eval select $BIG_TEST as using_big_test;
drop table if exists t1,t2,t3;
create table t1(id1 int not null auto_increment primary key, t char(12));
create table t2(id2 int not null, t char(12));
create table t3(id3 int not null, t char(12), index(id3));
disable_query_log;
let $1 = 100;
while ($1)
{
let $2 = 5;
eval insert into t1(t) values ('$1');
while ($2)
{
eval insert into t2(id2,t) values ($1,'$2');
let $3 = 10;
while ($3)
{
eval insert into t3(id3,t) values ($1,'$2');
dec $3;
}
dec $2;
}
dec $1;
}
enable_query_log;
select count(*) from t1 where id1 > 95;
select count(*) from t2 where id2 > 95;
select count(*) from t3 where id3 > 95;
update t1,t2,t3 set t1.t="aaa", t2.t="bbb", t3.t="cc" where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 90;
select count(*) from t1 where t = "aaa";
select count(*) from t1 where id1 > 90;
select count(*) from t2 where t = "bbb";
select count(*) from t2 where id2 > 90;
select count(*) from t3 where t = "cc";
select count(*) from t3 where id3 > 90;
delete t1.*, t2.*, t3.* from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 95;
check table t1, t2, t3;
select count(*) from t1 where id1 > 95;
select count(*) from t2 where id2 > 95;
select count(*) from t3 where id3 > 95;
delete t1, t2, t3 from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 5;
select count(*) from t1 where id1 > 5;
select count(*) from t2 where id2 > 5;
select count(*) from t3 where id3 > 5;
delete from t1, t2, t3 using t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 0;
# These queries will force a scan of the table
select count(*) from t1 where id1;
select count(*) from t2 where id2;
select count(*) from t3 where id3;
drop table t1,t2,t3;
create table t1(id1 int not null primary key, t varchar(100)) pack_keys = 1;
create table t2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1;
disable_query_log;
let $1 = 1000;
while ($1)
{
let $2 = 5;
eval insert into t1 values ($1,'aaaaaaaaaaaaaaaaaaaa');
while ($2)
{
eval insert into t2(id2,t) values ($1,'bbbbbbbbbbbbbbbbb');
dec $2;
}
dec $1;
}
enable_query_log;
delete t1 from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500;
drop table t1,t2;
DROP TABLE IF EXISTS a,b,c;
CREATE TABLE a (
id int(11) NOT NULL default '0',
name varchar(10) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO a VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
CREATE TABLE b (
id int(11) NOT NULL default '0',
name varchar(10) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO b VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
CREATE TABLE c (
id int(11) NOT NULL default '0',
mydate datetime default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO c VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22
00:00:00'),(7,'2002-07-22 00:00:00');
delete a,b,c from a,b,c
where to_days(now())-to_days(c.mydate)>=30
and c.id=a.id and c.id=b.id;
select * from c;
DROP TABLE IF EXISTS a,b,c;
|
