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
|
# ----------------------------------------------------
# Tests for the performance schema statement Digests.
# ----------------------------------------------------
# Test case to show behavior of statements digest when
# using a view
# Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
--source include/no_protocol.inc
CREATE TABLE test.v1 (a int, b int);
INSERT INTO test.v1 VALUES (1, 100), (2, 200), (3, 300);
CREATE TABLE test.t1 (a int, b int);
INSERT INTO test.t1 VALUES (1, 100), (2, 200), (3, 300);
ANALYZE TABLE v1, t1;
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
#
# test.v1 is a table.
# Every query here is different, and should have a different digest.
#
# The hypergraph optimizer skips EXPLAIN with the traditional format.
# Disable warnings so that the count of SHOW WARNINGS in
# performance_schema.events_statements_summary_by_digest is the same
# regardless of optimizer used.
--disable_warnings
EXPLAIN SELECT * from test.v1;
EXPLAIN SELECT * from test.v1 where a = 1;
EXPLAIN SELECT * from test.v1 where b > 100;
EXPLAIN SELECT a, b from test.v1;
EXPLAIN SELECT b, a from test.v1;
--enable_warnings
SELECT * from test.v1;
SELECT * from test.v1 where a = 1;
SELECT * from test.v1 where b > 100;
SELECT a, b from test.v1;
SELECT b, a from test.v1;
--echo #
--echo # DIGESTS SEEN ON TABLE
--echo #
SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, QUERY_SAMPLE_TEXT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY DIGEST_TEXT;
DROP TABLE test.v1;
CREATE VIEW test.v1 AS SELECT * FROM test.t1;
#
# test.v1 is now a view.
# the query digests should be unchanged.
#
--disable_warnings
EXPLAIN SELECT * from test.v1;
EXPLAIN SELECT * from test.v1 where a = 1;
EXPLAIN SELECT * from test.v1 where b > 100;
EXPLAIN SELECT a, b from test.v1;
EXPLAIN SELECT b, a from test.v1;
--enable_warnings
SELECT * from test.v1;
SELECT * from test.v1 where a = 1;
SELECT * from test.v1 where b > 100;
SELECT a, b from test.v1;
SELECT b, a from test.v1;
--echo #
--echo # DIGESTS SEEN ON VIEW
--echo #
SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, QUERY_SAMPLE_TEXT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY DIGEST_TEXT;
DROP VIEW test.v1;
DROP TABLE test.t1;
|
