Drizzled Public API Documentation

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 
41 using namespace std;
42 
43 namespace drizzled
44 {
45 
46 static 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 
63 static 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 
77 void 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 
427 bool 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 */
TODO: Rename this file - func.h is stupid.
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)
Definition: sql_select.cc:368
static const uint32_t UNCACHEABLE_EXPLAIN
forcing to save JOIN for explain
Definition: definitions.h:190