Debian Codegraph @

mysql-test/suite/sysschema/t/v_schema_tables_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 # Tests for sys schema
5 # Verify the sys.schema_tables_with_full_table_scans view
6
7 # Ensure structure changes don't slip in
8 DESC sys.schema_tables_with_full_table_scans;
9
10 # Make sure view select does not error, but ignore results
11 --disable_result_log
12 SELECT * FROM sys.schema_tables_with_full_table_scans;
13 --enable_result_log
14
15 # Ensure structure changes don't slip in
16 DESC sys.x$schema_tables_with_full_table_scans;
17
18 # Make sure view select does not error, but ignore results
19 --disable_result_log
20 SELECT * FROM sys.x$schema_tables_with_full_table_scans;
21 --enable_result_log
22
23 # Create a dummy table, force some full table scans, verify the results
24 CREATE TABLE test.t (i BIGINT AUTO_INCREMENT PRIMARY KEY, j BIGINT) ENGINE = innodb;
25
26 INSERT INTO test.t (j) VALUES (1), (2), (3);
27
28 INSERT INTO test.t (j) (SELECT j*2 FROM test.t);
29
30 INSERT INTO test.t (j) (SELECT j*2 FROM test.t);
31
32 INSERT INTO test.t (j) (SELECT j*2 FROM test.t);
33
34 INSERT INTO test.t (j) (SELECT j*2 FROM test.t);
35
36 INSERT INTO test.t (j) (SELECT j*2 FROM test.t);
37
38 INSERT INTO test.t (j) (SELECT j*2 FROM test.t);
39
40 INSERT INTO test.t (j) (SELECT j*2 FROM test.t);
41
42 INSERT INTO test.t (j) (SELECT j*2 FROM test.t);
43
44 # The table should now have 768 rows
45
46 # Now truncate the P_S tables, to get fresh results
47 CALL sys.ps_truncate_all_tables(false);
48
49 # Following returns 28 rows, but should full scan as j has no index
50 # Using RAND() to force no query caching, so ignore results, they are not important
51 --disable_ps2_protocol
52 --disable_result_log
53 SELECT i, j, RAND() FROM test.t WHERE j = 12;
54 --enable_result_log
55 --enable_ps2_protocol
56
57 # Ensure the base performance schema data is aggregated first
58 --let $wait_condition= SELECT COUNT_STAR = 768 FROM performance_schema.table_io_waits_summary_by_index_usage WHERE object_schema = 'test' AND object_name = 't' AND index_name IS NULL
59 --source include/wait_condition.inc
60
61 # Now verify the table shows up in the views with the right row count (should be 768)
62 SELECT object_schema, object_name, rows_full_scanned FROM sys.schema_tables_with_full_table_scans;
63
64 SELECT object_schema, object_name, rows_full_scanned FROM sys.x$schema_tables_with_full_table_scans;
65
66 # Scan again
67 --disable_ps2_protocol
68 --disable_result_log
69 SELECT i, j, RAND() FROM test.t WHERE j = 12;
70 --enable_result_log
71 --enable_ps2_protocol
72
73 # Again ensure the base performance schema data is aggregated first
74 --let $wait_condition= SELECT COUNT_STAR = 1536 FROM performance_schema.table_io_waits_summary_by_index_usage WHERE object_schema = 'test' AND object_name = 't' AND index_name IS NULL
75 --source include/wait_condition.inc
76
77 # Now verify that double the amount of rows in the table should be shown as scanned (should be 1536)
78 SELECT object_schema, object_name, rows_full_scanned FROM sys.schema_tables_with_full_table_scans;
79
80 SELECT object_schema, object_name, rows_full_scanned FROM sys.x$schema_tables_with_full_table_scans;
81
82 # Do a point lookup
83 SELECT * FROM test.t WHERE i = 10;
84
85 # The number of rows scanned should not have changed (should still be 1536)
86 SELECT object_schema, object_name, rows_full_scanned FROM sys.schema_tables_with_full_table_scans;
87
88 SELECT object_schema, object_name, rows_full_scanned FROM sys.x$schema_tables_with_full_table_scans;
89
90 # Cleanup
91 DROP TABLE test.t;