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
|
DESC sys.statements_with_full_table_scans;
Field Type Null Key Default Extra
query longtext YES NULL
db varchar(64) YES NULL
exec_count bigint unsigned NO NULL
total_latency varchar(11) YES NULL
no_index_used_count bigint unsigned NO NULL
no_good_index_used_count bigint unsigned NO NULL
no_index_used_pct decimal(24,0) NO 0
rows_sent bigint unsigned NO NULL
rows_examined bigint unsigned NO NULL
rows_sent_avg decimal(21,0) YES NULL
rows_examined_avg decimal(21,0) YES NULL
first_seen timestamp(6) NO NULL
last_seen timestamp(6) NO NULL
digest varchar(64) YES NULL
SELECT * FROM sys.statements_with_full_table_scans;
DESC sys.x$statements_with_full_table_scans;
Field Type Null Key Default Extra
query longtext YES NULL
db varchar(64) YES NULL
exec_count bigint unsigned NO NULL
total_latency bigint unsigned NO NULL
no_index_used_count bigint unsigned NO NULL
no_good_index_used_count bigint unsigned NO NULL
no_index_used_pct decimal(24,0) NO 0
rows_sent bigint unsigned NO NULL
rows_examined bigint unsigned NO NULL
rows_sent_avg decimal(21,0) YES NULL
rows_examined_avg decimal(21,0) YES NULL
first_seen timestamp(6) NO NULL
last_seen timestamp(6) NO NULL
digest varchar(64) YES NULL
SELECT * FROM sys.x$statements_with_full_table_scans;
CREATE DATABASE v_statements_with_full_table_scans;
CREATE TABLE v_statements_with_full_table_scans.t (i BIGINT AUTO_INCREMENT PRIMARY KEY, j BIGINT) ENGINE = innodb;
INSERT INTO v_statements_with_full_table_scans.t (j) VALUES (1), (2), (3);
INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
CALL sys.ps_truncate_all_tables(false);
summary
Truncated 49 tables
SELECT i, j, RAND() FROM v_statements_with_full_table_scans.t WHERE j = 12;
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;
db query rows_examined
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;
db query rows_examined
test SELECT `i` , `j` , `RAND` ( ) FROM `v_statements_with_full_table_scans` . `t` WHERE `j` = ? 768
SELECT i, j, RAND() FROM v_statements_with_full_table_scans.t WHERE j = 12;
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;
db query rows_examined
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;
db query rows_examined
test SELECT `i` , `j` , `RAND` ( ) FROM `v_statements_with_full_table_scans` . `t` WHERE `j` = ? 1536
SELECT * FROM v_statements_with_full_table_scans.t WHERE i = 10;
i j
10 4
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;
db query rows_examined
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;
db query rows_examined
test SELECT `i` , `j` , `RAND` ( ) FROM `v_statements_with_full_table_scans` . `t` WHERE `j` = ? 1536
DROP DATABASE v_statements_with_full_table_scans;
|
