Debian Codegraph @

mysql-test/suite/innodb/r/innodb_stats_persistent.result

raw
1 SET @saved_include_delete_marked = @@GLOBAL.innodb_stats_include_delete_marked;
2 SET GLOBAL innodb_stats_include_delete_marked = ON;
3 SET @saved_traditional = @@GLOBAL.innodb_stats_traditional;
4 SET GLOBAL innodb_stats_traditional=false;
5 SET @saved_modified_counter = @@GLOBAL.innodb_stats_modified_counter;
6 SET GLOBAL innodb_stats_modified_counter=1;
7 CREATE TABLE t1 (id SERIAL, val INT UNSIGNED NOT NULL, KEY(val))
8 ENGINE=INNODB STATS_PERSISTENT=1,STATS_AUTO_RECALC=1;
9 CREATE TABLE t2 LIKE t1;
10 INSERT INTO t1 (val) SELECT 4 FROM seq_1_to_16;
11 SET STATEMENT use_stat_tables=never FOR
12 ANALYZE TABLE t1;
13 Table Op Msg_type Msg_text
14 test.t1 analyze status OK
15 connect con1, localhost, root,,;
16 START TRANSACTION;
17 DELETE FROM t1;
18 SELECT COUNT(*) FROM t1;
19 connection default;
20 # With innodb_stats_include_delete_marked=ON,
21 # DELETE must not affect statistics before COMMIT.
22 EXPLAIN SELECT * FROM t1 WHERE val=4;
23 id select_type table type possible_keys key key_len ref rows Extra
24 1 SIMPLE t1 ref val val 4 const 1 Using index
25 connection con1;
26 COUNT(*)
27 0
28 ROLLBACK;
29 SELECT COUNT(*) FROM t1;
30 COUNT(*)
31 16
32 EXPLAIN SELECT * FROM t1 WHERE val=4;
33 id select_type table type possible_keys key key_len ref rows Extra
34 1 SIMPLE t1 ref val val 4 const 16 Using index
35 BEGIN;
36 DELETE FROM t1;
37 COMMIT;
38 SELECT COUNT(*) FROM t1;
39 COUNT(*)
40 0
41 connection default;
42 BEGIN;
43 INSERT INTO t2 (val) SELECT 4 FROM seq_1_to_16;
44 # The INSERT will show up before COMMIT.
45 EXPLAIN SELECT * FROM t2 WHERE val=4;
46 id select_type table type possible_keys key key_len ref rows Extra
47 1 SIMPLE t2 ref val val 4 const 16 Using index
48 SELECT COUNT(*) FROM t2;
49 COUNT(*)
50 16
51 # The ROLLBACK of the INSERT must affect the statistics.
52 ROLLBACK;
53 SELECT COUNT(*) FROM t2;
54 COUNT(*)
55 0
56 connection con1;
57 EXPLAIN SELECT * FROM t2 WHERE val=4;
58 id select_type table type possible_keys key key_len ref rows Extra
59 1 SIMPLE t2 ref val val 4 const 1 Using index
60 SET GLOBAL innodb_max_purge_lag_wait=0;
61 # After COMMIT and purge, the DELETE must show up.
62 EXPLAIN SELECT * FROM t1 WHERE val=4;
63 id select_type table type possible_keys key key_len ref rows Extra
64 1 SIMPLE t1 ref val val 4 const 1 Using index
65 SET GLOBAL innodb_stats_include_delete_marked = OFF;
66 BEGIN;
67 INSERT INTO t1 (val) SELECT 4 FROM seq_1_to_16;
68 EXPLAIN SELECT * FROM t1 WHERE val=4;
69 id select_type table type possible_keys key key_len ref rows Extra
70 1 SIMPLE t1 ref val val 4 const 16 Using index
71 ROLLBACK;
72 EXPLAIN SELECT * FROM t1 WHERE val=4;
73 id select_type table type possible_keys key key_len ref rows Extra
74 1 SIMPLE t1 ref val val 4 const 1 Using index
75 BEGIN;
76 INSERT INTO t1 (val) SELECT 4 FROM seq_1_to_16;
77 COMMIT;
78 EXPLAIN SELECT * FROM t1 WHERE val=4;
79 id select_type table type possible_keys key key_len ref rows Extra
80 1 SIMPLE t1 ref val val 4 const 16 Using index
81 BEGIN;
82 DELETE FROM t1;
83 SELECT COUNT(*) FROM t1;
84 COUNT(*)
85 0
86 # With innodb_stats_include_delete_marked=OFF,
87 # DELETE must affect statistics even before COMMIT.
88 # However, if there was a WHERE condition,
89 # ha_innobase::records_in_range() would count the delete-marked records.
90 EXPLAIN SELECT * FROM t1;
91 id select_type table type possible_keys key key_len ref rows Extra
92 1 SIMPLE t1 index NULL val 4 NULL 1 Using index
93 ROLLBACK;
94 EXPLAIN SELECT * FROM t1;
95 id select_type table type possible_keys key key_len ref rows Extra
96 1 SIMPLE t1 index NULL val 4 NULL 16 Using index
97 SELECT COUNT(*) FROM t1;
98 COUNT(*)
99 16
100 disconnect con1;
101 connection default;
102 DROP TABLE t1,t2;
103 SET GLOBAL innodb_stats_include_delete_marked = @saved_include_delete_marked;
104 SET GLOBAL innodb_stats_traditional = @saved_traditional;
105 SET GLOBAL innodb_stats_modified_counter = @saved_modified_counter;
106 CREATE TABLE bug12429573 (i INTEGER PRIMARY KEY, j INTEGER, KEY(j))
107 ENGINE=INNODB STATS_PERSISTENT=1;
108 SET STATEMENT use_stat_tables=never FOR
109 ANALYZE TABLE bug12429573;
110 Table Op Msg_type Msg_text
111 test.bug12429573 analyze status OK
112 SELECT last_update INTO @last FROM mysql.innodb_table_stats
113 WHERE table_name = 'bug12429573';
114 SELECT * FROM mysql.innodb_index_stats
115 WHERE table_name = 'bug12429573' AND last_update!=@last;
116 database_name table_name index_name last_update stat_name stat_value sample_size stat_description
117 SET STATEMENT use_stat_tables=never FOR
118 ANALYZE TABLE bug12429573;
119 Table Op Msg_type Msg_text
120 test.bug12429573 analyze status OK
121 SELECT * FROM mysql.innodb_table_stats
122 WHERE table_name = 'bug12429573' AND last_update=@last;
123 database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes
124 SELECT * FROM mysql.innodb_index_stats
125 WHERE table_name = 'bug12429573' AND last_update=@last;
126 database_name table_name index_name last_update stat_name stat_value sample_size stat_description
127 DROP TABLE bug12429573;