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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
|
#
# Performance Schema, Index validation
#
--echo
--echo ## Create index_test() procedure
--disable_warnings
DROP PROCEDURE IF EXISTS index_test;
--enable_warnings
--disable_query_log
# This procedure executes a SELECT for the given table, once with indexes disabled
# and again with indexes enabled. The two result sets are compared.
#
# 1. Run query with no indexes "USE INDEX ()".
# 2. Save results in temporary table, prefix rows with sequence number.
# 3. Run queries with indexes enabled.
# 4. Save results in temporary table, prefix rows with sequence number.
# 5. Compare tables for extra or missing rows with a JOIN on the sequence number.
#
# Parameters:
# table_name - Name of table
# where_clause - WHERE clause with indexes
DELIMITER $$;
CREATE PROCEDURE index_test(IN table_name VARCHAR(1024),
IN where_clause VARCHAR(1024))
BEGIN
DROP TABLE IF EXISTS test.indexes_off;
SET @table_name = table_name;
SET @where_clause = where_clause;
# Tables such as events_errors with TIMESTAMP columns, using "*" for the
# result set tables fails with "1067: Invalid default value for 'FIRST_SEEN'".
# As a workaround, specify only the necessary columns.
## Indexes disabled ##
# Create table from result set with autoincremented sequence for each row (use AUTOINC?)
SET @row:=0;
SET @query_no_idx = CONCAT("SELECT ", @pfs_columns," FROM performance_schema.", @table_name, " USE INDEX () ", @where_clause);
SET @main_query = CONCAT("CREATE TABLE test.indexes_off AS (SELECT @row:=@row+1 SEQ, output.* FROM (", @query_no_idx, ") output);");
PREPARE stmt1 FROM @main_query;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
# Index the sequence column -- needed for large joins
ALTER TABLE test.indexes_off ADD INDEX (seq);
## Indexes enabled ##
# Record the initial handler_read_key count
FLUSH STATUS;
SELECT variable_value INTO @read_key_before FROM performance_schema.session_status USE INDEX() WHERE variable_name = "handler_read_key";
SET @read_key_before := @read_key_before + 0;
DROP TABLE IF EXISTS test.indexes_on;
SET @row:=0;
SET @query = CONCAT("SELECT ", @pfs_columns," FROM performance_schema.", @table_name, " ", @where_clause);
SET @main_query = CONCAT("CREATE TABLE test.indexes_on AS (SELECT @row:=@row+1 SEQ, output.* FROM (", @query, ") output);");
SET @explain = CONCAT("EXPLAIN ", @query);
SELECT @query AS "========";
PREPARE stmt2 FROM @main_query;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
# Record the final handler_read_key count
SELECT variable_value INTO @read_key_after FROM performance_schema.session_status USE INDEX() WHERE variable_name = "handler_read_key";
SET @read_key_after := @read_key_after + 0;
# Index the sequence column -- needed for large joins
ALTER TABLE test.indexes_on ADD INDEX (seq);
#PREPARE stmt3 FROM @explain;
#EXECUTE stmt3;
#DEALLOCATE PREPARE stmt3;
#DEBUG
#SELECT * FROM test.indexes_off;
#SELECT * FROM test.indexes_on;
## Verify that the indexes were used
IF (@read_key_before >= @read_key_after) THEN
SET @msg = CONCAT('*** ERROR *** Handler_read_key before: ', @read_key_before, ' After: ', @read_key_after);
ELSE
SET @msg = "OK: Handler_read_key";
#DEBUG SET @msg = CONCAT('OK: Handler_read_key before: ', @read_key_before, ' After: ', @read_key_after);
END IF;
SELECT @msg AS "";
## Verify that the expected result set was generated
SELECT COUNT(*) > 0 INTO @expected FROM test.indexes_off;
SELECT COUNT(*) > 0 INTO @actual FROM test.indexes_on;
IF @expected = 1 THEN
SET @result_set = "Non-empty";
SET @bad_result_set = "Empty";
ELSE
SET @result_set = "Empty";
SET @bad_result_set = "Non-empty";
END IF;
IF @actual != @expected THEN
SET @result = CONCAT('*** ERROR ***: Result set: ', @bad_result_set, ' Expected: ', @result_set);
ELSE
SET @result = CONCAT('OK: Result set: ', @result_set, ' Expected: ', @result_set);
END IF;
SELECT @result AS "";
## Check for extra or missing rows
SELECT COUNT(*) INTO @extra_rows FROM test.indexes_off indexes_off LEFT JOIN test.indexes_on ON (indexes_off.seq = indexes_on.seq) WHERE indexes_on.seq IS NULL;
SELECT COUNT(*) INTO @missing_rows FROM test.indexes_on indexes_on RIGHT JOIN test.indexes_off ON (indexes_on.seq = indexes_off.seq) WHERE indexes_off.seq IS NULL;
SET @result = "OK: ";
IF (@extra_rows > 0) OR (@missing_rows > 0) THEN
SET @result = "*** ERROR ***: ";
END IF;
SET @rows = CONCAT(@result, 'Extra rows: ', @extra_rows, ' Missing rows: ', @missing_rows);
SELECT @rows AS "";
## Compare checksums
#CHECKSUM TABLE test.indexes_off;
#CHECKSUM TABLE test.indexes_on;
END$$
DELIMITER ;$$
--enable_query_log
|
