drizzle
C++ MySQL-protocol-compatible database server
Loading...
Searching...
No Matches
explain_plan.cc
1/* -*- mode: c++; c-basic-offset: 2; indent-tabs-mode: nil; -*-
2 * vim:expandtab:shiftwidth=2:tabstop=2:smarttab:
3 *
4 * Copyright (C) 2008-2009 Sun Microsystems, Inc.
5 *
6 * This program is free software; you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation; version 2 of the License.
9 *
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
14 *
15 * You should have received a copy of the GNU General Public License
16 * along with this program; if not, write to the Free Software
17 * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
18 */
19
20#include <config.h>
21
22#include <drizzled/session.h>
23#include <drizzled/item/uint.h>
24#include <drizzled/item/float.h>
25#include <drizzled/item/string.h>
26#include <drizzled/optimizer/explain_plan.h>
27#include <drizzled/optimizer/position.h>
28#include <drizzled/optimizer/quick_ror_intersect_select.h>
29#include <drizzled/optimizer/range.h>
30#include <drizzled/sql_select.h>
31#include <drizzled/join.h>
32#include <drizzled/internal/m_string.h>
33#include <drizzled/select_result.h>
34#include <drizzled/sql_lex.h>
35
36#include <cstdio>
37#include <string>
38#include <sstream>
39#include <bitset>
40
41using namespace std;
42
43namespace drizzled
44{
45
46static const string access_method_str[]=
47{
48 "UNKNOWN",
49 "system",
50 "const",
51 "eq_ref",
52 "ref",
53 "MAYBE_REF",
54 "ALL",
55 "range",
56 "index",
57 "ref_or_null",
58 "unique_subquery",
59 "index_subquery",
60 "index_merge"
61};
62
63static const string select_type_str[]=
64{
65 "PRIMARY",
66 "SIMPLE",
67 "DERIVED",
68 "DEPENDENT SUBQUERY",
69 "UNCACHEABLE SUBQUERY",
70 "SUBQUERY",
71 "DEPENDENT UNION",
72 "UNCACHEABLE_UNION",
73 "UNION",
74 "UNION RESULT"
75};
76
77void optimizer::ExplainPlan::printPlan()
78{
79 List<Item> item_list;
80 Session *session= join->session;
81 select_result *result= join->result;
82 Item *item_null= new Item_null();
83 const charset_info_st * const cs= system_charset_info;
84 int quick_type;
85 /* Don't log this into the slow query log */
86 session->server_status&= ~(SERVER_QUERY_NO_INDEX_USED | SERVER_QUERY_NO_GOOD_INDEX_USED);
87 join->unit->offset_limit_cnt= 0;
88
89 /*
90 NOTE: the number/types of items pushed into item_list must be in sync with
91 EXPLAIN column types as they're "defined" in Session::send_explain_fields()
92 */
93 if (message)
94 {
95 item_list.push_back(new Item_int((int32_t)join->select_lex->select_number));
96 item_list.push_back(new Item_string(select_type_str[join->select_lex->type], cs));
97 for (uint32_t i= 0; i < 7; i++)
98 item_list.push_back(item_null);
99
100 if (join->session->lex().describe & DESCRIBE_EXTENDED)
101 item_list.push_back(item_null);
102
103 item_list.push_back(new Item_string(str_ref(message), cs));
104 if (result->send_data(item_list))
105 join->error= 1;
106 }
107 else if (join->select_lex == join->unit->fake_select_lex)
108 {
109 /*
110 here we assume that the query will return at least two rows, so we
111 show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong
112 and no filesort will be actually done, but executing all selects in
113 the UNION to provide precise EXPLAIN information will hardly be
114 appreciated :)
115 */
116 char table_name_buffer[NAME_LEN];
117 item_list.clear();
118 /* id */
119 item_list.push_back(new Item_null);
120 /* select_type */
121 item_list.push_back(new Item_string(select_type_str[join->select_lex->type], cs));
122 /* table */
123 {
124 Select_Lex *sl= join->unit->first_select();
125 uint32_t len= 6, lastop= 0;
126 memcpy(table_name_buffer, STRING_WITH_LEN("<union"));
127 for (; sl && len + lastop + 5 < NAME_LEN; sl= sl->next_select())
128 {
129 len+= lastop;
130 lastop= snprintf(table_name_buffer + len, NAME_LEN - len,
131 "%u,", sl->select_number);
132 }
133 if (sl || len + lastop >= NAME_LEN)
134 {
135 memcpy(table_name_buffer + len, STRING_WITH_LEN("...>") + 1);
136 len+= 4;
137 }
138 else
139 {
140 len+= lastop;
141 table_name_buffer[len - 1]= '>'; // change ',' to '>'
142 }
143 item_list.push_back(new Item_string(table_name_buffer, len, cs));
144 }
145 /* type */
146 item_list.push_back(new Item_string(access_method_str[AM_ALL], cs));
147 /* possible_keys */
148 item_list.push_back(item_null);
149 /* key*/
150 item_list.push_back(item_null);
151 /* key_len */
152 item_list.push_back(item_null);
153 /* ref */
154 item_list.push_back(item_null);
155 /* in_rows */
156 if (join->session->lex().describe & DESCRIBE_EXTENDED)
157 item_list.push_back(item_null);
158 /* rows */
159 item_list.push_back(item_null);
160 /* extra */
161 if (join->unit->global_parameters->order_list.first)
162 item_list.push_back(new Item_string(str_ref("Using filesort"), cs));
163 else
164 item_list.push_back(new Item_string(str_ref(""), cs));
165
166 if (result->send_data(item_list))
167 join->error= 1;
168 }
169 else
170 {
171 table_map used_tables= 0;
172 for (uint32_t i= 0; i < join->tables; i++)
173 {
174 JoinTable *tab= join->join_tab + i;
175 Table *table= tab->table;
176 char keylen_str_buf[64];
177 string extra;
178 char table_name_buffer[NAME_LEN];
179 string tmp1;
180 string tmp2;
181 string tmp3;
182
183 quick_type= -1;
184 item_list.clear();
185 /* id */
186 item_list.push_back(new Item_uint((uint32_t)join->select_lex->select_number));
187 /* select_type */
188 item_list.push_back(new Item_string(select_type_str[join->select_lex->type], cs));
189 if (tab->type == AM_ALL && tab->select && tab->select->quick)
190 {
191 quick_type= tab->select->quick->get_type();
192 if ((quick_type == optimizer::QuickSelectInterface::QS_TYPE_INDEX_MERGE) ||
193 (quick_type == optimizer::QuickSelectInterface::QS_TYPE_ROR_INTERSECT) ||
194 (quick_type == optimizer::QuickSelectInterface::QS_TYPE_ROR_UNION))
195 tab->type = AM_INDEX_MERGE;
196 else
197 tab->type = AM_RANGE;
198 }
199 /* table */
200 if (table->derived_select_number)
201 {
202 /* Derived table name generation */
203 int len= snprintf(table_name_buffer,
204 sizeof(table_name_buffer)-1,
205 "<derived%u>",
206 table->derived_select_number);
207 item_list.push_back(new Item_string(table_name_buffer, len, cs));
208 }
209 else
210 {
211 TableList *real_table= table->pos_in_table_list;
212 item_list.push_back(new Item_string(str_ref(real_table->alias), cs));
213 }
214 /* "type" column */
215 item_list.push_back(new Item_string(access_method_str[tab->type], cs));
216 /* Build "possible_keys" value and add it to item_list */
217 if (tab->keys.any())
218 {
219 for (uint32_t j= 0; j < table->getShare()->sizeKeys(); j++)
220 {
221 if (tab->keys.test(j))
222 {
223 if (tmp1.length())
224 tmp1.append(",");
225 tmp1.append(table->key_info[j].name,
226 strlen(table->key_info[j].name));
227 }
228 }
229 }
230 if (tmp1.length())
231 item_list.push_back(new Item_string(tmp1, cs));
232 else
233 item_list.push_back(item_null);
234
235 /* Build "key", "key_len", and "ref" values and add them to item_list */
236 if (tab->ref.key_parts)
237 {
238 KeyInfo *key_info= table->key_info+ tab->ref.key;
239 item_list.push_back(new Item_string(str_ref(key_info->name), system_charset_info));
240 uint32_t length= internal::int64_t2str(tab->ref.key_length, keylen_str_buf, 10) - keylen_str_buf;
241 item_list.push_back(new Item_string(keylen_str_buf, length, system_charset_info));
242 for (StoredKey **ref= tab->ref.key_copy; *ref; ref++)
243 {
244 if (tmp2.length())
245 tmp2.append(",");
246 tmp2.append((*ref)->name(),
247 strlen((*ref)->name()));
248 }
249 item_list.push_back(new Item_string(tmp2, cs));
250 }
251 else if (tab->type == AM_NEXT)
252 {
253 KeyInfo *key_info=table->key_info+ tab->index;
254 item_list.push_back(new Item_string(str_ref(key_info->name),cs));
255 uint32_t length= internal::int64_t2str(key_info->key_length, keylen_str_buf, 10) - keylen_str_buf;
256 item_list.push_back(new Item_string(keylen_str_buf, length, system_charset_info));
257 item_list.push_back(item_null);
258 }
259 else if (tab->select && tab->select->quick)
260 {
261 tab->select->quick->add_keys_and_lengths(&tmp2, &tmp3);
262 item_list.push_back(new Item_string(tmp2, cs));
263 item_list.push_back(new Item_string(tmp3, cs));
264 item_list.push_back(item_null);
265 }
266 else
267 {
268 item_list.push_back(item_null);
269 item_list.push_back(item_null);
270 item_list.push_back(item_null);
271 }
272
273 /* Add "rows" field to item_list. */
274 double examined_rows;
275 if (tab->select && tab->select->quick)
276 {
277 examined_rows= tab->select->quick->records;
278 }
279 else if (tab->type == AM_NEXT || tab->type == AM_ALL)
280 {
281 examined_rows= tab->limit ? tab->limit : tab->table->cursor->records();
282 }
283 else
284 {
285 optimizer::Position cur_pos= join->getPosFromOptimalPlan(i);
286 examined_rows= cur_pos.getFanout();
287 }
288
289 item_list.push_back(new Item_int((int64_t) (uint64_t) examined_rows,
290 MY_INT64_NUM_DECIMAL_DIGITS));
291
292 /* Add "filtered" field to item_list. */
293 if (join->session->lex().describe & DESCRIBE_EXTENDED)
294 {
295 float f= 0.0;
296 if (examined_rows)
297 {
298 optimizer::Position cur_pos= join->getPosFromOptimalPlan(i);
299 f= static_cast<float>(100.0 * cur_pos.getFanout() / examined_rows);
300 }
301 item_list.push_back(new Item_float(f, 2));
302 }
303
304 /* Build "Extra" field and add it to item_list. */
305 bool key_read= table->key_read;
306 if ((tab->type == AM_NEXT || tab->type == AM_CONST) &&
307 table->covering_keys.test(tab->index))
308 key_read= 1;
309 if (quick_type == optimizer::QuickSelectInterface::QS_TYPE_ROR_INTERSECT &&
310 ! ((optimizer::QuickRorIntersectSelect *) tab->select->quick)->need_to_fetch_row)
311 key_read= 1;
312
313 if (tab->info)
314 item_list.push_back(new Item_string(str_ref(tab->info),cs));
315 else if (tab->packed_info & TAB_INFO_HAVE_VALUE)
316 {
317 if (tab->packed_info & TAB_INFO_USING_INDEX)
318 extra.append("; Using index");
319 if (tab->packed_info & TAB_INFO_USING_WHERE)
320 extra.append("; Using where");
321 if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
322 extra.append("; Full scan on NULL key");
323 if (extra.length())
324 extra.erase(0, 2); /* Skip initial "; "*/
325 item_list.push_back(new Item_string(extra, cs));
326 }
327 else
328 {
329 if (quick_type == optimizer::QuickSelectInterface::QS_TYPE_ROR_UNION ||
330 quick_type == optimizer::QuickSelectInterface::QS_TYPE_ROR_INTERSECT ||
331 quick_type == optimizer::QuickSelectInterface::QS_TYPE_INDEX_MERGE)
332 {
333 extra.append("; Using ");
334 tab->select->quick->add_info_string(&extra);
335 }
336 if (tab->select)
337 {
338 if (tab->use_quick == 2)
339 {
340 /*
341 * To print out the bitset in tab->keys, we go through
342 * it 32 bits at a time. We need to do this to ensure
343 * that the to_ulong() method will not throw an
344 * out_of_range exception at runtime which would happen
345 * if the bitset we were working with was larger than 64
346 * bits on a 64-bit platform (for example).
347 */
348 stringstream s, w;
349 string str;
350 w << tab->keys;
351 w >> str;
352 for (uint32_t pos= 0; pos < tab->keys.size(); pos+= 32)
353 {
354 bitset<32> tmp(str, pos, 32);
355 if (tmp.any())
356 s << uppercase << hex << tmp.to_ulong();
357 }
358 extra.append("; Range checked for each record (index map: 0x");
359 extra.append(s.str().c_str());
360 extra.append(")");
361 }
362 else if (tab->select->cond)
363 {
364 extra.append("; Using where");
365 }
366 }
367 if (key_read)
368 {
369 if (quick_type == optimizer::QuickSelectInterface::QS_TYPE_GROUP_MIN_MAX)
370 extra.append("; Using index for group-by");
371 else
372 extra.append("; Using index");
373 }
374 if (table->reginfo.not_exists_optimize)
375 extra.append("; Not exists");
376
377 if (need_tmp_table)
378 {
379 need_tmp_table=0;
380 extra.append("; Using temporary");
381 }
382 if (need_order)
383 {
384 need_order=0;
385 extra.append("; Using filesort");
386 }
387 if (distinct & test_all_bits(used_tables,session->used_tables))
388 extra.append("; Distinct");
389
390 if (tab->insideout_match_tab)
391 {
392 extra.append("; LooseScan");
393 }
394
395 for (uint32_t part= 0; part < tab->ref.key_parts; part++)
396 {
397 if (tab->ref.cond_guards[part])
398 {
399 extra.append("; Full scan on NULL key");
400 break;
401 }
402 }
403
404 if (i > 0 && tab[-1].next_select == sub_select_cache)
405 extra.append("; Using join buffer");
406
407 if (extra.length())
408 extra.erase(0, 2);
409 item_list.push_back(new Item_string(extra, cs));
410 }
411 // For next iteration
412 used_tables|=table->map;
413 if (result->send_data(item_list))
414 join->error= 1;
415 }
416 }
417 for (Select_Lex_Unit *unit= join->select_lex->first_inner_unit();
418 unit;
419 unit= unit->next_unit())
420 {
421 if (explainUnion(session, unit, result))
422 return;
423 }
424 return;
425}
426
427bool optimizer::ExplainPlan::explainUnion(Session *session,
428 Select_Lex_Unit *unit,
429 select_result *result)
430{
431 bool res= false;
432 Select_Lex *first= unit->first_select();
433
434 for (Select_Lex *sl= first;
435 sl;
436 sl= sl->next_select())
437 {
438 // drop UNCACHEABLE_EXPLAIN, because it is for internal usage only
439 sl->uncacheable.reset(UNCACHEABLE_EXPLAIN);
440 if (&session->lex().select_lex == sl)
441 {
442 if (sl->first_inner_unit() || sl->next_select())
443 {
444 sl->type= optimizer::ST_PRIMARY;
445 }
446 else
447 {
448 sl->type= optimizer::ST_SIMPLE;
449 }
450 }
451 else
452 {
453 if (sl == first)
454 {
455 if (sl->linkage == DERIVED_TABLE_TYPE)
456 {
457 sl->type= optimizer::ST_DERIVED;
458 }
459 else
460 {
461 if (sl->uncacheable.test(UNCACHEABLE_DEPENDENT))
462 {
463 sl->type= optimizer::ST_DEPENDENT_SUBQUERY;
464 }
465 else
466 {
467 if (sl->uncacheable.any())
468 {
469 sl->type= optimizer::ST_UNCACHEABLE_SUBQUERY;
470 }
471 else
472 {
473 sl->type= optimizer::ST_SUBQUERY;
474 }
475 }
476 }
477 }
478 else
479 {
480 if (sl->uncacheable.test(UNCACHEABLE_DEPENDENT))
481 {
482 sl->type= optimizer::ST_DEPENDENT_UNION;
483 }
484 else
485 {
486 if (sl->uncacheable.any())
487 {
488 sl->type= optimizer::ST_UNCACHEABLE_UNION;
489 }
490 else
491 {
492 sl->type= optimizer::ST_UNION;
493 }
494 }
495 }
496 }
497 sl->options|= SELECT_DESCRIBE;
498 }
499
500 if (unit->is_union())
501 {
502 unit->fake_select_lex->select_number= UINT_MAX; // just for initialization
503 unit->fake_select_lex->type= optimizer::ST_UNION_RESULT;
504 unit->fake_select_lex->options|= SELECT_DESCRIBE;
505 if (! (res= unit->prepare(session, result, SELECT_NO_UNLOCK | SELECT_DESCRIBE)))
506 {
507 res= unit->exec();
508 }
509 res|= unit->cleanup();
510 }
511 else
512 {
513 session->lex().current_select= first;
514 unit->set_limit(unit->global_parameters);
515 res= select_query(session,
516 &first->ref_pointer_array,
517 (TableList*) first->table_list.first,
518 first->with_wild,
519 first->item_list,
520 first->where,
521 first->order_list.elements + first->group_list.elements,
522 (Order*) first->order_list.first,
523 (Order*) first->group_list.first,
524 first->having,
525 first->options | session->options | SELECT_DESCRIBE,
526 result,
527 unit,
528 first);
529 }
530 return (res || session->is_error());
531}
532
533} /* namespace drizzled */
bool select_query(Session *session, Item ***rref_pointer_array, TableList *tables, uint32_t wild_num, List< Item > &fields, COND *conds, uint32_t og_num, Order *order, Order *group, Item *having, uint64_t select_options, select_result *result, Select_Lex_Unit *unit, Select_Lex *select_lex)
TODO: Rename this file - func.h is stupid.
static const uint32_t UNCACHEABLE_EXPLAIN
forcing to save JOIN for explain
STL namespace.