Debian Codegraph @

mysql-test/suite/sysschema/t/v_statements_with_full_table_scans.test

raw
1 -- source include/not_embedded.inc
2 -- source include/have_innodb.inc
3 -- source ../include/ps_truncate_all_tables.inc
4 # Performance schema tracks prepared statements separately, and does not
5 # yet have a summary view that we can use for this view.
6 # Until then, we disable this test with --ps-protocol
7 -- source include/no_protocol.inc
8
9 # Tests for sys schema
10 # Verify the sys.statements_with_full_table_scans view
11
12 # Ensure structure changes don't slip in
13 DESC sys.statements_with_full_table_scans;
14
15 # Make sure view select does not error, but ignore results
16 --disable_result_log
17 SELECT * FROM sys.statements_with_full_table_scans;
18 --enable_result_log
19
20
21 # Ensure structure changes don't slip in
22 DESC sys.x$statements_with_full_table_scans;
23
24 # Make sure view select does not error, but ignore results
25 --disable_result_log
26 SELECT * FROM sys.x$statements_with_full_table_scans;
27 --enable_result_log
28
29 # Create a dummy table, force some full table scans, verify the results
30 CREATE DATABASE v_statements_with_full_table_scans;
31
32 CREATE TABLE v_statements_with_full_table_scans.t (i BIGINT AUTO_INCREMENT PRIMARY KEY, j BIGINT) ENGINE = innodb;
33
34 INSERT INTO v_statements_with_full_table_scans.t (j) VALUES (1), (2), (3);
35
36 INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
37
38 INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
39
40 INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
41
42 INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
43
44 INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
45
46 INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
47
48 INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
49
50 INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
51
52 # The table should now have 768 rows
53
54 # Now truncate the P_S tables, to get fresh results
55 CALL sys.ps_truncate_all_tables(false);
56
57 # Following returns 28 rows, but should full scan as j has no index
58 # Using RAND() to force no query caching, so ignore results, they are not important
59 --disable_result_log
60 SELECT i, j, RAND() FROM v_statements_with_full_table_scans.t WHERE j = 12;
61 --enable_result_log
62
63 # Now verify the statement shows up in the views with the right row count (should be 768)
64 SELECT db, query, rows_examined FROM sys.statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC;
65
66 SELECT db, query, rows_examined FROM sys.x$statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC;
67
68 # Scan again
69 --disable_result_log
70 SELECT i, j, RAND() FROM v_statements_with_full_table_scans.t WHERE j = 12;
71 --enable_result_log
72
73 # Now verify that double the amount of rows for the statement should be shown as scanned (should be 1536)
74 SELECT db, query, rows_examined FROM sys.statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC;
75
76 SELECT db, query, rows_examined FROM sys.x$statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC;
77
78 # Do a point lookup
79 SELECT * FROM v_statements_with_full_table_scans.t WHERE i = 10;
80
81 # The number of rows scanned should not have changed (should still be 1536)
82 SELECT db, query, rows_examined FROM sys.statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC;
83
84 SELECT db, query, rows_examined FROM sys.x$statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC;
85
86 # Cleanup
87 DROP DATABASE v_statements_with_full_table_scans;