statement_digest.result 6.49 KB
Newer Older
1 2 3 4 5 6 7 8 9 10
####################################
# SETUP
####################################
CREATE DATABASE statements_digest;
USE statements_digest;
CREATE TABLE t1(a int);
CREATE TABLE t2(a int);
CREATE TABLE t3(a int, b int);
CREATE TABLE t4(a int, b int);
CREATE TABLE t5(a int, b int, c int);
11 12 13
CREATE TABLE t6(a int, b int, c int, d int);
CREATE TABLE t11 (c CHAR(4));
CREATE TABLE t12 (c CHAR(4));
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
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
####################################
# EXECUTION
####################################
SELECT 1 FROM t1;
1
SELECT 1 FROM `t1`;
1
SELECT 1,2 FROM t1;
1	2
SELECT 1, 2, 3, 4 FROM t1;
1	2	3	4
SELECT 1 FROM t2;
1
SELECT 1,2 FROM t2;
1	2
SELECT 1, 2, 3, 4 FROM t2;
1	2	3	4
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
INSERT INTO t3 VALUES (1, 2);
INSERT INTO t4 VALUES (1, 2);
INSERT INTO t5 VALUES (1, 2, 3);
INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t1 VALUES (1), (2), (3), (4);
INSERT INTO t3 VALUES (1, 2), (3, 4), (5, 6);
INSERT INTO t5 VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);
41 42 43 44 45 46 47 48 49
INSERT INTO t1 VALUES (NULL);
INSERT INTO t3 VALUES (NULL,NULL);
INSERT INTO t3 VALUES (1,NULL);
INSERT INTO t3 VALUES (NULL,1);
INSERT INTO t6 VALUES (NULL, NULL, NULL, NULL);
INSERT INTO t6 VALUES (1, NULL, NULL, NULL);
INSERT INTO t6 VALUES (NULL, 2, NULL, NULL);
INSERT INTO t6 VALUES (1, 2, 3, NULL);
INSERT INTO t6 VALUES (1, 2, NULL, 4);
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
SELECT                                          1           +        1;
1           +        1
2
SELECT 1;
1
1
SELECT 1 /* This is an inline comment */ + 1;
1 /* This is an inline comment */ + 1
2
SELECT 1+
/*
this is a
multiple-line comment
*/
1;
1+
/*
this is a
multiple-line comment
*/
1
2
CREATE SCHEMA statements_digest_temp;
DROP SCHEMA statements_digest_temp;
CREATE DATABASE statements_digest_temp;
DROP DATABASE statements_digest_temp;
76 77 78 79 80 81 82
SELECT 1 FROM no_such_table;
ERROR 42S02: Table 'statements_digest.no_such_table' doesn't exist
CREATE TABLE dup_table (c char(4));
CREATE TABLE dup_table (c char(4));
ERROR 42S01: Table 'dup_table' already exists
DROP TABLE dup_table;
INSERT INTO t11 VALUES("MySQL");
83 84
Warnings:
Warning	1265	Data truncated for column 'c' at row 1
85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108
PREPARE stmt FROM "SELECT * FROM t12";
EXECUTE stmt;
c
EXECUTE stmt;
c
DEALLOCATE PREPARE stmt;
CREATE PROCEDURE p1() BEGIN SELECT * FROM t12; END//
CALL p1();
c
CALL p1();
c
DROP PROCEDURE p1;
CREATE FUNCTION `func`(a INT, b INT) RETURNS int(11) RETURN a+b //
select func(3,4);
func(3,4)
7
select func(13,42);
func(13,42)
55
DROP FUNCTION func;
CREATE TRIGGER trg BEFORE INSERT ON t12 FOR EACH ROW SET @a:=1;
INSERT INTO t12 VALUES ("abc");
INSERT INTO t12 VALUES ("def");
DROP TRIGGER trg;
109 110 111
####################################
# QUERYING PS STATEMENT DIGEST
####################################
112
SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_WARNINGS,
113
SUM_ERRORS FROM performance_schema.events_statements_summary_by_digest;
114
SCHEMA_NAME	DIGEST	DIGEST_TEXT	COUNT_STAR	SUM_ROWS_AFFECTED	SUM_WARNINGS	SUM_ERRORS
Sergei Golubchik's avatar
Sergei Golubchik committed
115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151
statements_digest	35a1dd67493f13a363597c041f170f3a	TRUNCATE TABLE performance_schema . events_statements_summary_by_digest 	1	0	0	0
statements_digest	26dfbd081fcf7f115c1d0469a486a359	SELECT ? FROM t1 	1	0	0	0
statements_digest	3f616e69aa50da84e83c0fb36e2097a8	SELECT ? FROM `t1` 	1	0	0	0
statements_digest	76856b08e7f7d5abb33be516e1f67774	SELECT ?, ... FROM t1 	2	0	0	0
statements_digest	633ab3778bbdfd8d19fbc374096260f6	SELECT ? FROM t2 	1	0	0	0
statements_digest	8327dfae0814061f44ed8dffa9dc6435	SELECT ?, ... FROM t2 	2	0	0	0
statements_digest	839fb88fc8518181883672c04cb63f49	INSERT INTO t1 VALUES (?) 	1	1	0	0
statements_digest	2ba9122b6d1fc0a088f7bda1288fa09c	INSERT INTO t2 VALUES (?) 	1	1	0	0
statements_digest	8b6edf8409045279d23afdcafb84bfed	INSERT INTO t3 VALUES (...) 	4	4	0	0
statements_digest	bb8eaef8479c82506d33f77ddb5c28aa	INSERT INTO t4 VALUES (...) 	1	1	0	0
statements_digest	f994e1321d5b06de770ff4831308bac4	INSERT INTO t5 VALUES (...) 	1	1	0	0
statements_digest	0dba4196aed38abb1519d6244403cb17	INSERT INTO t1 VALUES (?) /* , ... */ 	2	7	0	0
statements_digest	92f8ab3ae191fe475bfadc774fa94043	INSERT INTO t3 VALUES (...) /* , ... */ 	1	3	0	0
statements_digest	3137bda2c67d2e56caf769ec6ce82659	INSERT INTO t5 VALUES (...) /* , ... */ 	1	3	0	0
statements_digest	9422d1121371a267a413108efc89baf9	INSERT INTO t1 VALUES ( NULL ) 	1	1	0	0
statements_digest	5f5d0d6bedf99549b5ebbe3a405328da	INSERT INTO t6 VALUES (...) 	5	5	0	0
statements_digest	37db9f5b30f6aab26e96da3b4a1453e8	SELECT ? + ? 	3	0	0	0
statements_digest	3a5796055ae14f5e4b6e4d8544f02f6e	SELECT ? 	1	0	0	0
statements_digest	45116c5e93c234b72dd9a3abcefc88c5	CREATE SCHEMA statements_digest_temp 	2	2	0	0
statements_digest	ced4041d0418bc07691afc32d83136a3	DROP SCHEMA statements_digest_temp 	2	0	0	0
statements_digest	15a9faf206e03a368cff0515fdc66e49	SELECT ? FROM no_such_table 	1	0	0	1
statements_digest	e972017fec3cc0ed0faae39fac081252	CREATE TABLE dup_table ( c CHARACTER (?) ) 	2	0	0	1
statements_digest	b7c43333163beb24690a9a48e9183345	DROP TABLE dup_table 	1	0	0	0
statements_digest	9bfd4998690c1d2a177545f1222ae63d	INSERT INTO t11 VALUES (?) 	1	1	1	0
statements_digest	c574ec5908ae7f1cf1381d5d20e5c0fa	SHOW WARNINGS 	1	0	0	0
statements_digest	fb36b794ae04d52a096cc57a7f019b68	PREPARE stmt FROM ? 	1	0	0	0
statements_digest	c865e57de1bfb41a6508d4ab11451604	EXECUTE stmt 	2	0	0	0
statements_digest	260a16ff0083fe404cd7e9f4b769ba9a	DEALLOCATE PREPARE stmt 	1	0	0	0
statements_digest	32e03dcacd4f3c6c48919405da64db7f	CREATE PROCEDURE p1 ( ) BEGIN SELECT * FROM t12 ; END 	1	0	0	0
statements_digest	2350c8efd325c3037260912f4819fcc4	CALL p1 ( ) 	2	0	0	0
statements_digest	d109091bb6714e45fd781adf6b46804e	DROP PROCEDURE p1 	1	0	0	0
statements_digest	8dc1dbd5a0f430ff529d9e6305253a9a	CREATE FUNCTION `func` ( a INTEGER , b INTEGER ) RETURNS INTEGER (?) RETURN a + b 	1	0	0	0
statements_digest	dd5c4f35de90b2c4c0fb9a7013d8f255	SELECT func (...) 	2	0	0	0
statements_digest	61a04e3100cef2fe94313ed4014e2eae	DROP FUNCTION func 	1	0	0	0
statements_digest	b118ca18d17608e117c88d2678296ec6	CREATE TRIGGER trg BEFORE INSERT ON t12 FOR EACH ROW SET @ ? := ? 	1	0	0	0
statements_digest	a6f5c29379cdd3edfe561f13e276ea31	INSERT INTO t12 VALUES (?) 	2	2	0	0
statements_digest	ad26862793866ae1fe5dbb1ab4381c05	DROP TRIGGER trg 	1	0	0	0
152 153 154 155 156 157 158 159
####################################
# CLEANUP
####################################
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
DROP TABLE IF EXISTS t4;
DROP TABLE IF EXISTS t5;
160 161 162
DROP TABLE IF EXISTS t6;
DROP TABLE IF EXISTS t11;
DROP TABLE IF EXISTS t12;
163
DROP DATABASE IF EXISTS statements_digest;