Debian Codegraph @

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

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