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
|
DESC sys.schema_tables_with_full_table_scans;
Field Type Null Key Default Extra
object_schema varchar(64) YES NULL
object_name varchar(64) YES NULL
rows_full_scanned bigint(20) unsigned NO NULL
latency varchar(12) YES NULL
SELECT * FROM sys.schema_tables_with_full_table_scans;
DESC sys.x$schema_tables_with_full_table_scans;
Field Type Null Key Default Extra
object_schema varchar(64) YES NULL
object_name varchar(64) YES NULL
rows_full_scanned bigint(20) unsigned NO NULL
latency bigint(20) unsigned NO NULL
SELECT * FROM sys.x$schema_tables_with_full_table_scans;
CREATE TABLE test.t (i BIGINT AUTO_INCREMENT PRIMARY KEY, j BIGINT) ENGINE = innodb;
INSERT INTO test.t (j) VALUES (1), (2), (3);
INSERT INTO test.t (j) (SELECT j*2 FROM test.t);
INSERT INTO test.t (j) (SELECT j*2 FROM test.t);
INSERT INTO test.t (j) (SELECT j*2 FROM test.t);
INSERT INTO test.t (j) (SELECT j*2 FROM test.t);
INSERT INTO test.t (j) (SELECT j*2 FROM test.t);
INSERT INTO test.t (j) (SELECT j*2 FROM test.t);
INSERT INTO test.t (j) (SELECT j*2 FROM test.t);
INSERT INTO test.t (j) (SELECT j*2 FROM test.t);
CALL sys.ps_truncate_all_tables(false);
summary
Truncated 44 tables
SELECT i, j, RAND() FROM test.t WHERE j = 12;
SELECT object_schema, object_name, rows_full_scanned FROM sys.schema_tables_with_full_table_scans;
object_schema object_name rows_full_scanned
test t 768
SELECT object_schema, object_name, rows_full_scanned FROM sys.x$schema_tables_with_full_table_scans;
object_schema object_name rows_full_scanned
test t 768
SELECT i, j, RAND() FROM test.t WHERE j = 12;
SELECT object_schema, object_name, rows_full_scanned FROM sys.schema_tables_with_full_table_scans;
object_schema object_name rows_full_scanned
test t 1536
SELECT object_schema, object_name, rows_full_scanned FROM sys.x$schema_tables_with_full_table_scans;
object_schema object_name rows_full_scanned
test t 1536
SELECT * FROM test.t WHERE i = 10;
i j
10 4
SELECT object_schema, object_name, rows_full_scanned FROM sys.schema_tables_with_full_table_scans;
object_schema object_name rows_full_scanned
test t 1536
SELECT object_schema, object_name, rows_full_scanned FROM sys.x$schema_tables_with_full_table_scans;
object_schema object_name rows_full_scanned
test t 1536
DROP TABLE test.t;
|