Debian Codegraph @

mysql-test/suite/ndb/t/ndbinfo_plans.test

raw
1 --source include/have_ndb.inc
2 --source include/elide_costs.inc
3
4 --vertical_results
5 --result_format 2
6
7 use ndbinfo;
8 ## Look for "Extra: Select tables optimized away" in the following
9 ## ``EXPLAIN SELECT COUNT(*)'' from compiled-in ndbinfo tables
10 ##
11
12 ## ndb$tables
13 select count(*) from ndb$tables;
14 explain select count(*) from ndb$tables;
15
16 ## dict_obj_types
17 select count(*) from dict_obj_types;
18 let $extra= query_get_value(explain select count(*) from dict_obj_types, Extra, 1);
19 echo $extra;
20
21 ## error_messages
22 let $extra= query_get_value(explain select count(*) from error_messages, Extra, 1);
23 echo $extra;
24
25 ## config_params
26 let $extra= query_get_value(explain select count(*) from config_params, Extra, 1);
27 echo $extra;
28
29 ## blocks
30 let $extra= query_get_value(explain select count(*) from blocks, Extra, 1);
31 echo $extra;
32
33 ## backup_id
34 let $extra= query_get_value(explain select count(*) from backup_id, Extra, 1);
35 echo $extra;
36
37 ## `EXPLAIN SELECT COUNT(*)'' from a normal ndbinfo table. Extra will be NULL.
38 let $extra= query_get_value(explain select count(*) from counters, Extra, 1);
39 echo $extra;
40
41 ## Information schema reveals row counts as seen by optimizer
42 ## (but filter out cpu-related tables where results are not predictable)
43 set ndbinfo_show_hidden=1;
44 --horizontal_results
45 SELECT table_name, table_rows, avg_row_length
46 FROM information_schema.tables
47 WHERE table_schema='ndbinfo' AND table_type = 'BASE TABLE'
48 AND table_name not like '%cpu%'
49 ORDER BY table_name;
50
51 --disable_query_log
52 CREATE TEMPORARY TABLE rowcounts
53 SELECT table_name, table_rows as est_rows, 0 as actual_rows
54 FROM information_schema.tables
55 WHERE table_schema='ndbinfo' AND table_type = 'BASE TABLE'
56 AND table_name not like '%cpu%';
57
58 ##
59 ## Compare estimated sizes of ndbinfo tables to actual size.
60 --delimiter |;
61 CREATE PROCEDURE populate_sizes()
62 BEGIN
63 DECLARE done INT DEFAULT FALSE;
64 DECLARE name varchar(64);
65 DECLARE cur CURSOR FOR SELECT table_name from rowcounts;
66 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
67
68 PREPARE stmt1 FROM "UPDATE rowcounts SET actual_rows=? where table_name=?";
69 open cur;
70 read_loop: LOOP
71 IF done then
72 LEAVE read_loop;
73 END IF;
74 FETCH cur into name;
75 SET @name = name;
76 SET @sql = concat("select count(*) FROM ", name, " INTO @actual");
77 PREPARE stmt2 FROM @sql;
78 EXECUTE stmt2;
79 DEALLOCATE PREPARE stmt2;
80 EXECUTE stmt1 USING @actual, @name;
81 END LOOP;
82 DEALLOCATE PREPARE stmt1;
83 CLOSE cur;
84 END|;
85 --delimiter ;
86 --enable_query_log
87
88 ## List the tables where estimated size equals actual size.
89 CALL populate_sizes();
90 SELECT count(*) from rowcounts WHERE est_rows = actual_rows;
91 SELECT table_name from rowcounts WHERE est_rows = actual_rows
92 ORDER BY table_name;
93
94 ## Check that no tables have zero estimates
95 SELECT table_name from rowcounts WHERE est_rows = 0;
96
97 DROP TEMPORARY TABLE rowcounts;
98 DROP PROCEDURE populate_sizes;
99
100 ## Show the indexes on virtual tables
101 SELECT table_name, constraint_name, column_name, ordinal_position
102 FROM information_schema.key_column_usage
103 WHERE table_schema = 'ndbinfo'
104 ORDER BY table_name;
105
106 SELECT * FROM blocks ORDER BY block_number DESC;
107
108 --vertical_results
109
110 ## Query results validate that index seek operations are correct,
111 ## and plans show use of the index
112 select * from blocks where block_number=245;
113 let $key= query_get_value(explain select * from blocks where block_number=245,key,1);
114 echo $key;
115 assert($key == "PRIMARY");
116
117 select * from blocks where block_number in (250,251);
118
119 --replace_regex $elide_costs
120 explain format=tree select * from blocks where block_number in (250,251);
121
122 --replace_regex $elide_costs
123 explain format=tree select * from blocks order by block_number;
124
125 --replace_regex $elide_costs
126 explain format=tree select * from blocks where block_number < 250;
127
128 ## Can scan backwards:
129 --replace_regex $elide_costs
130 explain format=tree select * from blocks where block_number > 250
131 order by block_number desc;
132
133 select * from dict_obj_types where type_id = 6;
134
135 ## No row at 7:
136 select * from dict_obj_types where type_id = 7;
137
138 select * from dict_obj_types where type_id in (1,2,6);
139
140 let $key= query_get_value(explain select * from dict_obj_types where type_id = 6,key,1);
141 echo $key;
142 assert($key == "PRIMARY");
143
144 select state_int_value, state_name from ndb$dblqh_tcconnect_state where state_int_value = 9;
145
146 select param_number, param_name from ndb$config_params where param_number = 161;
147 let $key= query_get_value(explain select param_name from config_params where param_number = 161, key, 1);
148 echo $key;
149 assert($key == "PRIMARY");
150
151 ## No row:
152 select param_name from ndb$config_params where param_number = 1;
153 select param_name from ndb$config_params where param_number = 40000;
154
155 ## The plan for counters
156 --replace_regex $elide_costs
157 explain format=tree select * from counters;
158
159 ## The plan for memory_per_fragment
160 --replace_regex $elide_costs
161 explain format=tree select * from memory_per_fragment;
162
163 ## WL#11968 tables and views
164 ##
165 # Get event_id's by table name, this is to be independent
166 # on which id has been assigned
167 let $event_ids = `SELECT GROUP_CONCAT(event_id)
168 FROM events e JOIN dictionary_tables t using(table_id)
169 WHERE table_name IN ('ndb_schema',
170 'ndb_schema_result',
171 'ndb_index_stat_head',
172 'ndb_apply_status')
173 GROUP BY 'all'`;
174 #echo event_ids: $event_ids;
175 --replace_result $event_ids <event_ids>
176 --horizontal_results
177 eval SELECT e.name, table_name
178 FROM events e JOIN dictionary_tables t using(table_id)
179 WHERE event_id IN ($event_ids) ORDER BY table_name;
180
181 ## Query uses primary keys on both tables:
182 --replace_result $event_ids <event_ids>
183 eval EXPLAIN SELECT event_id, e.name, table_name FROM events e
184 JOIN dictionary_tables t using(table_id)
185 WHERE event_id IN ($event_ids) ORDER BY event_id;
186
187 SELECT * from events where event_id = -1;
188 SELECT table_event from events where event_id = 1;
189 SELECT table_name from dictionary_tables where table_id = -1;
190 SELECT table_name from dictionary_tables where table_id = 9990;
191 SELECT table_name from dictionary_tables where table_id = 5;
192 SELECT table_name from dictionary_tables where table_id = 6;
193
194 select * from blobs;
195 select * from index_columns order by index_object_id;
196 select * from hash_maps order by id limit 1;
197
198 --vertical_results
199 ## Create an all-MyISAM version of memory_per_fragment for comparison
200 ## (now with a primary key in the MyISAM version as well)
201 ## and explain the same query
202 --disable_query_log
203 --disable_result_log
204 use test;
205 CREATE TABLE ndb$frag_mem_use engine=myisam
206 as select * from ndbinfo.ndb$frag_mem_use;
207 CREATE TABLE ndb$dict_obj_info engine=myisam
208 as select * from ndbinfo.ndb$dict_obj_info limit 14;
209 CREATE TABLE ndb$dict_obj_types engine=myisam
210 as select * from ndbinfo.ndb$dict_obj_types;
211 ALTER TABLE ndb$dict_obj_types ADD PRIMARY KEY (type_id);
212
213 CREATE VIEW memory_per_fragment AS
214 SELECT name.fq_name, parent_name.fq_name AS parent_fq_name,
215 types.type_name AS type, table_id, node_id, block_instance,
216 fragment_num, fixed_elem_alloc_bytes, fixed_elem_free_bytes,
217 fixed_elem_size_bytes, fixed_elem_count,
218 FLOOR(fixed_elem_free_bytes/fixed_elem_size_bytes) AS
219 fixed_elem_free_count, var_elem_alloc_bytes, var_elem_free_bytes,
220 var_elem_count, hash_index_alloc_bytes
221 FROM ndb$frag_mem_use AS space
222 JOIN ndb$dict_obj_info AS name
223 ON name.id=space.table_id AND name.type<=6
224 JOIN ndb$dict_obj_types AS types
225 ON name.type=types.type_id
226 LEFT JOIN ndb$dict_obj_info AS parent_name
227 ON name.parent_obj_id=parent_name.id AND
228 name.parent_obj_type=parent_name.type;
229 --enable_query_log
230 --enable_result_log
231
232 --replace_regex $elide_costs
233 explain format=tree select * from memory_per_fragment;
234
235
236 ## CLEANUP
237 --disable_query_log
238 --disable_result_log
239 DROP VIEW memory_per_fragment;
240 DROP TABLE ndb$frag_mem_use , ndb$dict_obj_info , ndb$dict_obj_types;
241