Debian Codegraph @

mysql-test/suite/perfschema/include/idx_compare_setup_index_test.inc

raw
1 #
2 # Performance Schema, Index validation
3 #
4
5 --echo
6 --echo ## Create index_test() procedure
7 --disable_warnings
8 DROP PROCEDURE IF EXISTS index_test;
9 --enable_warnings
10
11
12 --disable_query_log
13
14 # This procedure executes a SELECT for the given table, once with indexes disabled
15 # and again with indexes enabled. The two result sets are compared.
16 #
17 # 1. Run query with no indexes "USE INDEX ()".
18 # 2. Save results in temporary table, prefix rows with sequence number.
19 # 3. Run queries with indexes enabled.
20 # 4. Save results in temporary table, prefix rows with sequence number.
21 # 5. Compare tables for extra or missing rows with a JOIN on the sequence number.
22 #
23 # Parameters:
24 # table_name - Name of table
25 # where_clause - WHERE clause with indexes
26
27 DELIMITER $$;
28 CREATE PROCEDURE index_test(IN table_name VARCHAR(1024),
29 IN where_clause VARCHAR(1024))
30 BEGIN
31 DROP TABLE IF EXISTS test.indexes_off;
32 SET @table_name = table_name;
33 SET @where_clause = where_clause;
34
35 # Tables such as events_errors with TIMESTAMP columns, using "*" for the
36 # result set tables fails with "1067: Invalid default value for 'FIRST_SEEN'".
37 # As a workaround, specify only the necessary columns.
38
39 ## Indexes disabled ##
40
41 # Create table from result set with autoincremented sequence for each row (use AUTOINC?)
42 SET @row:=0;
43 SET @query_no_idx = CONCAT("SELECT ", @pfs_columns," FROM performance_schema.", @table_name, " USE INDEX () ", @where_clause);
44 SET @main_query = CONCAT("CREATE TABLE test.indexes_off AS (SELECT @row:=@row+1 SEQ, output.* FROM (", @query_no_idx, ") output);");
45
46 PREPARE stmt1 FROM @main_query;
47 EXECUTE stmt1;
48 DEALLOCATE PREPARE stmt1;
49 # Index the sequence column -- needed for large joins
50 ALTER TABLE test.indexes_off ADD INDEX (seq);
51
52 ## Indexes enabled ##
53
54 # Record the initial handler_read_key count
55 FLUSH STATUS;
56 SELECT variable_value INTO @read_key_before FROM performance_schema.session_status USE INDEX() WHERE variable_name = "handler_read_key";
57 SET @read_key_before := @read_key_before + 0;
58
59 DROP TABLE IF EXISTS test.indexes_on;
60 SET @row:=0;
61 SET @query = CONCAT("SELECT ", @pfs_columns," FROM performance_schema.", @table_name, " ", @where_clause);
62 SET @main_query = CONCAT("CREATE TABLE test.indexes_on AS (SELECT @row:=@row+1 SEQ, output.* FROM (", @query, ") output);");
63 SET @explain = CONCAT("EXPLAIN ", @query);
64 SELECT @query AS "========";
65
66 PREPARE stmt2 FROM @main_query;
67 EXECUTE stmt2;
68 DEALLOCATE PREPARE stmt2;
69
70 # Record the final handler_read_key count
71 SELECT variable_value INTO @read_key_after FROM performance_schema.session_status USE INDEX() WHERE variable_name = "handler_read_key";
72 SET @read_key_after := @read_key_after + 0;
73
74 # Index the sequence column -- needed for large joins
75 ALTER TABLE test.indexes_on ADD INDEX (seq);
76
77 #PREPARE stmt3 FROM @explain;
78 #EXECUTE stmt3;
79 #DEALLOCATE PREPARE stmt3;
80
81 #DEBUG
82 #SELECT * FROM test.indexes_off;
83 #SELECT * FROM test.indexes_on;
84
85 ## Verify that the indexes were used
86
87 IF (@read_key_before >= @read_key_after) THEN
88 SET @msg = CONCAT('*** ERROR *** Handler_read_key before: ', @read_key_before, ' After: ', @read_key_after);
89 ELSE
90 SET @msg = "OK: Handler_read_key";
91 #DEBUG SET @msg = CONCAT('OK: Handler_read_key before: ', @read_key_before, ' After: ', @read_key_after);
92 END IF;
93 SELECT @msg AS "";
94
95 ## Verify that the expected result set was generated
96
97 SELECT COUNT(*) > 0 INTO @expected FROM test.indexes_off;
98 SELECT COUNT(*) > 0 INTO @actual FROM test.indexes_on;
99
100 IF @expected = 1 THEN
101 SET @result_set = "Non-empty";
102 SET @bad_result_set = "Empty";
103 ELSE
104 SET @result_set = "Empty";
105 SET @bad_result_set = "Non-empty";
106 END IF;
107
108 IF @actual != @expected THEN
109 SET @result = CONCAT('*** ERROR ***: Result set: ', @bad_result_set, ' Expected: ', @result_set);
110 ELSE
111 SET @result = CONCAT('OK: Result set: ', @result_set, ' Expected: ', @result_set);
112 END IF;
113
114 SELECT @result AS "";
115
116 ## Check for extra or missing rows
117
118 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;
119 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;
120
121 SET @result = "OK: ";
122 IF (@extra_rows > 0) OR (@missing_rows > 0) THEN
123 SET @result = "*** ERROR ***: ";
124 END IF;
125 SET @rows = CONCAT(@result, 'Extra rows: ', @extra_rows, ' Missing rows: ', @missing_rows);
126 SELECT @rows AS "";
127
128 ## Compare checksums
129 #CHECKSUM TABLE test.indexes_off;
130 #CHECKSUM TABLE test.indexes_on;
131
132 END$$
133 DELIMITER ;$$
134
135 --enable_query_log
136