blob: 971e399a3b63080bae7c7d99a71589e825cb32cf (
plain)
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
108
109
110
111
112
113
114
115
116
117
118
|
flush status;
show status like 'Table_lock%';
Variable_name Value
Table_locks_immediate 0
Table_locks_waited 0
SET SQL_LOG_BIN=0;
drop table if exists t1;
create table t1(n int) engine=myisam;
insert into t1 values(1);
lock tables t1 read;
unlock tables;
lock tables t1 read;
update t1 set n = 3;
unlock tables;
show status like 'Table_lock%';
Variable_name Value
Table_locks_immediate 3
Table_locks_waited 1
drop table t1;
select 1;
1
1
show status like 'last_query_cost';
Variable_name Value
Last_query_cost 0.000000
FLUSH STATUS;
SHOW STATUS LIKE 'max_used_connections';
Variable_name Value
Max_used_connections 1
SET @save_thread_cache_size=@@thread_cache_size;
SET GLOBAL thread_cache_size=3;
SHOW STATUS LIKE 'max_used_connections';
Variable_name Value
Max_used_connections 3
FLUSH STATUS;
SHOW STATUS LIKE 'max_used_connections';
Variable_name Value
Max_used_connections 2
SHOW STATUS LIKE 'max_used_connections';
Variable_name Value
Max_used_connections 3
SHOW STATUS LIKE 'max_used_connections';
Variable_name Value
Max_used_connections 4
SET GLOBAL thread_cache_size=@save_thread_cache_size;
CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (1), (2);
SELECT a FROM t1 LIMIT 1;
a
1
SHOW SESSION STATUS LIKE 'Last_query_cost';
Variable_name Value
Last_query_cost 2.402418
EXPLAIN SELECT a FROM t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
SHOW SESSION STATUS LIKE 'Last_query_cost';
Variable_name Value
Last_query_cost 2.402418
SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a;
a
1
2
SHOW SESSION STATUS LIKE 'Last_query_cost';
Variable_name Value
Last_query_cost 0.000000
EXPLAIN SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
2 UNION t1 ALL NULL NULL NULL NULL 2
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using filesort
SHOW SESSION STATUS LIKE 'Last_query_cost';
Variable_name Value
Last_query_cost 0.000000
SELECT a IN (SELECT a FROM t1) FROM t1 LIMIT 1;
a IN (SELECT a FROM t1)
1
SHOW SESSION STATUS LIKE 'Last_query_cost';
Variable_name Value
Last_query_cost 0.000000
SELECT (SELECT a FROM t1 LIMIT 1) x FROM t1 LIMIT 1;
x
1
SHOW SESSION STATUS LIKE 'Last_query_cost';
Variable_name Value
Last_query_cost 0.000000
SELECT * FROM t1 a, t1 b LIMIT 1;
a a
1 1
SHOW SESSION STATUS LIKE 'Last_query_cost';
Variable_name Value
Last_query_cost 4.805836
DROP TABLE t1;
DROP PROCEDURE IF EXISTS p1;
DROP FUNCTION IF EXISTS f1;
CREATE FUNCTION f1() RETURNS INTEGER
BEGIN
DECLARE foo INTEGER;
DECLARE bar INTEGER;
SET foo=1;
SET bar=2;
RETURN foo;
END $$
CREATE PROCEDURE p1()
BEGIN
SELECT 1;
END $$
SELECT f1();
f1()
1
CALL p1();
1
1
SELECT 9;
9
9
DROP PROCEDURE p1;
DROP FUNCTION f1;
|
