00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020 #include <config.h>
00021
00022 #include <drizzled/session.h>
00023 #include <drizzled/item/uint.h>
00024 #include <drizzled/item/float.h>
00025 #include <drizzled/item/string.h>
00026 #include <drizzled/optimizer/explain_plan.h>
00027 #include <drizzled/optimizer/position.h>
00028 #include <drizzled/optimizer/quick_ror_intersect_select.h>
00029 #include <drizzled/optimizer/range.h>
00030 #include <drizzled/sql_select.h>
00031 #include <drizzled/join.h>
00032 #include <drizzled/internal/m_string.h>
00033 #include <drizzled/select_result.h>
00034 #include <drizzled/sql_lex.h>
00035
00036 #include <cstdio>
00037 #include <string>
00038 #include <sstream>
00039 #include <bitset>
00040
00041 using namespace std;
00042
00043 namespace drizzled
00044 {
00045
00046 static const string access_method_str[]=
00047 {
00048 "UNKNOWN",
00049 "system",
00050 "const",
00051 "eq_ref",
00052 "ref",
00053 "MAYBE_REF",
00054 "ALL",
00055 "range",
00056 "index",
00057 "ref_or_null",
00058 "unique_subquery",
00059 "index_subquery",
00060 "index_merge"
00061 };
00062
00063 static const string select_type_str[]=
00064 {
00065 "PRIMARY",
00066 "SIMPLE",
00067 "DERIVED",
00068 "DEPENDENT SUBQUERY",
00069 "UNCACHEABLE SUBQUERY",
00070 "SUBQUERY",
00071 "DEPENDENT UNION",
00072 "UNCACHEABLE_UNION",
00073 "UNION",
00074 "UNION RESULT"
00075 };
00076
00077 void optimizer::ExplainPlan::printPlan()
00078 {
00079 List<Item> item_list;
00080 Session *session= join->session;
00081 select_result *result= join->result;
00082 Item *item_null= new Item_null();
00083 const CHARSET_INFO * const cs= system_charset_info;
00084 int quick_type;
00085
00086 session->server_status&= ~(SERVER_QUERY_NO_INDEX_USED | SERVER_QUERY_NO_GOOD_INDEX_USED);
00087 join->unit->offset_limit_cnt= 0;
00088
00089
00090
00091
00092
00093 if (message)
00094 {
00095 item_list.push_back(new Item_int((int32_t)
00096 join->select_lex->select_number));
00097 item_list.push_back(new Item_string(select_type_str[join->select_lex->type].c_str(),
00098 select_type_str[join->select_lex->type].length(),
00099 cs));
00100 for (uint32_t i= 0; i < 7; i++)
00101 item_list.push_back(item_null);
00102
00103 if (join->session->lex().describe & DESCRIBE_EXTENDED)
00104 item_list.push_back(item_null);
00105
00106 item_list.push_back(new Item_string(message,strlen(message),cs));
00107 if (result->send_data(item_list))
00108 join->error= 1;
00109 }
00110 else if (join->select_lex == join->unit->fake_select_lex)
00111 {
00112
00113
00114
00115
00116
00117
00118
00119 char table_name_buffer[NAME_LEN];
00120 item_list.clear();
00121
00122 item_list.push_back(new Item_null);
00123
00124 item_list.push_back(new Item_string(select_type_str[join->select_lex->type].c_str(),
00125 select_type_str[join->select_lex->type].length(),
00126 cs));
00127
00128 {
00129 Select_Lex *sl= join->unit->first_select();
00130 uint32_t len= 6, lastop= 0;
00131 memcpy(table_name_buffer, STRING_WITH_LEN("<union"));
00132 for (; sl && len + lastop + 5 < NAME_LEN; sl= sl->next_select())
00133 {
00134 len+= lastop;
00135 lastop= snprintf(table_name_buffer + len, NAME_LEN - len,
00136 "%u,", sl->select_number);
00137 }
00138 if (sl || len + lastop >= NAME_LEN)
00139 {
00140 memcpy(table_name_buffer + len, STRING_WITH_LEN("...>") + 1);
00141 len+= 4;
00142 }
00143 else
00144 {
00145 len+= lastop;
00146 table_name_buffer[len - 1]= '>';
00147 }
00148 item_list.push_back(new Item_string(table_name_buffer, len, cs));
00149 }
00150
00151 item_list.push_back(new Item_string(access_method_str[AM_ALL].c_str(),
00152 access_method_str[AM_ALL].length(),
00153 cs));
00154
00155 item_list.push_back(item_null);
00156
00157 item_list.push_back(item_null);
00158
00159 item_list.push_back(item_null);
00160
00161 item_list.push_back(item_null);
00162
00163 if (join->session->lex().describe & DESCRIBE_EXTENDED)
00164 item_list.push_back(item_null);
00165
00166 item_list.push_back(item_null);
00167
00168 if (join->unit->global_parameters->order_list.first)
00169 item_list.push_back(new Item_string("Using filesort",
00170 14,
00171 cs));
00172 else
00173 item_list.push_back(new Item_string("", 0, cs));
00174
00175 if (result->send_data(item_list))
00176 join->error= 1;
00177 }
00178 else
00179 {
00180 table_map used_tables= 0;
00181 for (uint32_t i= 0; i < join->tables; i++)
00182 {
00183 JoinTable *tab= join->join_tab + i;
00184 Table *table= tab->table;
00185 char keylen_str_buf[64];
00186 string extra;
00187 char table_name_buffer[NAME_LEN];
00188 string tmp1;
00189 string tmp2;
00190 string tmp3;
00191
00192 quick_type= -1;
00193 item_list.clear();
00194
00195 item_list.push_back(new Item_uint((uint32_t)
00196 join->select_lex->select_number));
00197
00198 item_list.push_back(new Item_string(select_type_str[join->select_lex->type].c_str(),
00199 select_type_str[join->select_lex->type].length(),
00200 cs));
00201 if (tab->type == AM_ALL && tab->select && tab->select->quick)
00202 {
00203 quick_type= tab->select->quick->get_type();
00204 if ((quick_type == optimizer::QuickSelectInterface::QS_TYPE_INDEX_MERGE) ||
00205 (quick_type == optimizer::QuickSelectInterface::QS_TYPE_ROR_INTERSECT) ||
00206 (quick_type == optimizer::QuickSelectInterface::QS_TYPE_ROR_UNION))
00207 tab->type = AM_INDEX_MERGE;
00208 else
00209 tab->type = AM_RANGE;
00210 }
00211
00212 if (table->derived_select_number)
00213 {
00214
00215 int len= snprintf(table_name_buffer,
00216 sizeof(table_name_buffer)-1,
00217 "<derived%u>",
00218 table->derived_select_number);
00219 item_list.push_back(new Item_string(table_name_buffer, len, cs));
00220 }
00221 else
00222 {
00223 TableList *real_table= table->pos_in_table_list;
00224 item_list.push_back(new Item_string(real_table->alias,
00225 strlen(real_table->alias),
00226 cs));
00227 }
00228
00229 item_list.push_back(new Item_string(access_method_str[tab->type].c_str(),
00230 access_method_str[tab->type].length(),
00231 cs));
00232
00233 if (tab->keys.any())
00234 {
00235 for (uint32_t j= 0; j < table->getShare()->sizeKeys(); j++)
00236 {
00237 if (tab->keys.test(j))
00238 {
00239 if (tmp1.length())
00240 tmp1.append(",");
00241 tmp1.append(table->key_info[j].name,
00242 strlen(table->key_info[j].name));
00243 }
00244 }
00245 }
00246 if (tmp1.length())
00247 item_list.push_back(new Item_string(tmp1.c_str(),tmp1.length(),cs));
00248 else
00249 item_list.push_back(item_null);
00250
00251
00252 if (tab->ref.key_parts)
00253 {
00254 KeyInfo *key_info= table->key_info+ tab->ref.key;
00255 item_list.push_back(new Item_string(key_info->name,
00256 strlen(key_info->name),
00257 system_charset_info));
00258 uint32_t length= internal::int64_t2str(tab->ref.key_length, keylen_str_buf, 10) -
00259 keylen_str_buf;
00260 item_list.push_back(new Item_string(keylen_str_buf,
00261 length,
00262 system_charset_info));
00263 for (StoredKey **ref= tab->ref.key_copy; *ref; ref++)
00264 {
00265 if (tmp2.length())
00266 tmp2.append(",");
00267 tmp2.append((*ref)->name(),
00268 strlen((*ref)->name()));
00269 }
00270 item_list.push_back(new Item_string(tmp2.c_str(),tmp2.length(),cs));
00271 }
00272 else if (tab->type == AM_NEXT)
00273 {
00274 KeyInfo *key_info=table->key_info+ tab->index;
00275 item_list.push_back(new Item_string(key_info->name,
00276 strlen(key_info->name),cs));
00277 uint32_t length= internal::int64_t2str(key_info->key_length, keylen_str_buf, 10) -
00278 keylen_str_buf;
00279 item_list.push_back(new Item_string(keylen_str_buf,
00280 length,
00281 system_charset_info));
00282 item_list.push_back(item_null);
00283 }
00284 else if (tab->select && tab->select->quick)
00285 {
00286 tab->select->quick->add_keys_and_lengths(&tmp2, &tmp3);
00287 item_list.push_back(new Item_string(tmp2.c_str(),tmp2.length(),cs));
00288 item_list.push_back(new Item_string(tmp3.c_str(),tmp3.length(),cs));
00289 item_list.push_back(item_null);
00290 }
00291 else
00292 {
00293 item_list.push_back(item_null);
00294 item_list.push_back(item_null);
00295 item_list.push_back(item_null);
00296 }
00297
00298
00299 double examined_rows;
00300 if (tab->select && tab->select->quick)
00301 {
00302 examined_rows= tab->select->quick->records;
00303 }
00304 else if (tab->type == AM_NEXT || tab->type == AM_ALL)
00305 {
00306 examined_rows= tab->limit ? tab->limit : tab->table->cursor->records();
00307 }
00308 else
00309 {
00310 optimizer::Position cur_pos= join->getPosFromOptimalPlan(i);
00311 examined_rows= cur_pos.getFanout();
00312 }
00313
00314 item_list.push_back(new Item_int((int64_t) (uint64_t) examined_rows,
00315 MY_INT64_NUM_DECIMAL_DIGITS));
00316
00317
00318 if (join->session->lex().describe & DESCRIBE_EXTENDED)
00319 {
00320 float f= 0.0;
00321 if (examined_rows)
00322 {
00323 optimizer::Position cur_pos= join->getPosFromOptimalPlan(i);
00324 f= static_cast<float>(100.0 * cur_pos.getFanout() / examined_rows);
00325 }
00326 item_list.push_back(new Item_float(f, 2));
00327 }
00328
00329
00330 bool key_read= table->key_read;
00331 if ((tab->type == AM_NEXT || tab->type == AM_CONST) &&
00332 table->covering_keys.test(tab->index))
00333 key_read= 1;
00334 if (quick_type == optimizer::QuickSelectInterface::QS_TYPE_ROR_INTERSECT &&
00335 ! ((optimizer::QuickRorIntersectSelect *) tab->select->quick)->need_to_fetch_row)
00336 key_read= 1;
00337
00338 if (tab->info)
00339 item_list.push_back(new Item_string(tab->info,strlen(tab->info),cs));
00340 else if (tab->packed_info & TAB_INFO_HAVE_VALUE)
00341 {
00342 if (tab->packed_info & TAB_INFO_USING_INDEX)
00343 extra.append("; Using index");
00344 if (tab->packed_info & TAB_INFO_USING_WHERE)
00345 extra.append("; Using where");
00346 if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
00347 extra.append("; Full scan on NULL key");
00348 if (extra.length())
00349 extra.erase(0, 2);
00350 item_list.push_back(new Item_string(extra.c_str(), extra.length(), cs));
00351 }
00352 else
00353 {
00354 uint32_t keyno= MAX_KEY;
00355 if (tab->ref.key_parts)
00356 keyno= tab->ref.key;
00357 else if (tab->select && tab->select->quick)
00358 keyno = tab->select->quick->index;
00359
00360 if (quick_type == optimizer::QuickSelectInterface::QS_TYPE_ROR_UNION ||
00361 quick_type == optimizer::QuickSelectInterface::QS_TYPE_ROR_INTERSECT ||
00362 quick_type == optimizer::QuickSelectInterface::QS_TYPE_INDEX_MERGE)
00363 {
00364 extra.append("; Using ");
00365 tab->select->quick->add_info_string(&extra);
00366 }
00367 if (tab->select)
00368 {
00369 if (tab->use_quick == 2)
00370 {
00371
00372
00373
00374
00375
00376
00377
00378
00379 stringstream s, w;
00380 string str;
00381 w << tab->keys;
00382 w >> str;
00383 for (uint32_t pos= 0; pos < tab->keys.size(); pos+= 32)
00384 {
00385 bitset<32> tmp(str, pos, 32);
00386 if (tmp.any())
00387 s << uppercase << hex << tmp.to_ulong();
00388 }
00389 extra.append("; Range checked for each record (index map: 0x");
00390 extra.append(s.str().c_str());
00391 extra.append(")");
00392 }
00393 else if (tab->select->cond)
00394 {
00395 extra.append("; Using where");
00396 }
00397 }
00398 if (key_read)
00399 {
00400 if (quick_type == optimizer::QuickSelectInterface::QS_TYPE_GROUP_MIN_MAX)
00401 extra.append("; Using index for group-by");
00402 else
00403 extra.append("; Using index");
00404 }
00405 if (table->reginfo.not_exists_optimize)
00406 extra.append("; Not exists");
00407
00408 if (need_tmp_table)
00409 {
00410 need_tmp_table=0;
00411 extra.append("; Using temporary");
00412 }
00413 if (need_order)
00414 {
00415 need_order=0;
00416 extra.append("; Using filesort");
00417 }
00418 if (distinct & test_all_bits(used_tables,session->used_tables))
00419 extra.append("; Distinct");
00420
00421 if (tab->insideout_match_tab)
00422 {
00423 extra.append("; LooseScan");
00424 }
00425
00426 for (uint32_t part= 0; part < tab->ref.key_parts; part++)
00427 {
00428 if (tab->ref.cond_guards[part])
00429 {
00430 extra.append("; Full scan on NULL key");
00431 break;
00432 }
00433 }
00434
00435 if (i > 0 && tab[-1].next_select == sub_select_cache)
00436 extra.append("; Using join buffer");
00437
00438 if (extra.length())
00439 extra.erase(0, 2);
00440 item_list.push_back(new Item_string(extra.c_str(), extra.length(), cs));
00441 }
00442
00443 used_tables|=table->map;
00444 if (result->send_data(item_list))
00445 join->error= 1;
00446 }
00447 }
00448 for (Select_Lex_Unit *unit= join->select_lex->first_inner_unit();
00449 unit;
00450 unit= unit->next_unit())
00451 {
00452 if (explainUnion(session, unit, result))
00453 return;
00454 }
00455 return;
00456 }
00457
00458 bool optimizer::ExplainPlan::explainUnion(Session *session,
00459 Select_Lex_Unit *unit,
00460 select_result *result)
00461 {
00462 bool res= false;
00463 Select_Lex *first= unit->first_select();
00464
00465 for (Select_Lex *sl= first;
00466 sl;
00467 sl= sl->next_select())
00468 {
00469
00470 sl->uncacheable.reset(UNCACHEABLE_EXPLAIN);
00471 if (&session->lex().select_lex == sl)
00472 {
00473 if (sl->first_inner_unit() || sl->next_select())
00474 {
00475 sl->type= optimizer::ST_PRIMARY;
00476 }
00477 else
00478 {
00479 sl->type= optimizer::ST_SIMPLE;
00480 }
00481 }
00482 else
00483 {
00484 if (sl == first)
00485 {
00486 if (sl->linkage == DERIVED_TABLE_TYPE)
00487 {
00488 sl->type= optimizer::ST_DERIVED;
00489 }
00490 else
00491 {
00492 if (sl->uncacheable.test(UNCACHEABLE_DEPENDENT))
00493 {
00494 sl->type= optimizer::ST_DEPENDENT_SUBQUERY;
00495 }
00496 else
00497 {
00498 if (sl->uncacheable.any())
00499 {
00500 sl->type= optimizer::ST_UNCACHEABLE_SUBQUERY;
00501 }
00502 else
00503 {
00504 sl->type= optimizer::ST_SUBQUERY;
00505 }
00506 }
00507 }
00508 }
00509 else
00510 {
00511 if (sl->uncacheable.test(UNCACHEABLE_DEPENDENT))
00512 {
00513 sl->type= optimizer::ST_DEPENDENT_UNION;
00514 }
00515 else
00516 {
00517 if (sl->uncacheable.any())
00518 {
00519 sl->type= optimizer::ST_UNCACHEABLE_UNION;
00520 }
00521 else
00522 {
00523 sl->type= optimizer::ST_UNION;
00524 }
00525 }
00526 }
00527 }
00528 sl->options|= SELECT_DESCRIBE;
00529 }
00530
00531 if (unit->is_union())
00532 {
00533 unit->fake_select_lex->select_number= UINT_MAX;
00534 unit->fake_select_lex->type= optimizer::ST_UNION_RESULT;
00535 unit->fake_select_lex->options|= SELECT_DESCRIBE;
00536 if (! (res= unit->prepare(session, result, SELECT_NO_UNLOCK | SELECT_DESCRIBE)))
00537 {
00538 res= unit->exec();
00539 }
00540 res|= unit->cleanup();
00541 }
00542 else
00543 {
00544 session->lex().current_select= first;
00545 unit->set_limit(unit->global_parameters);
00546 res= select_query(session,
00547 &first->ref_pointer_array,
00548 (TableList*) first->table_list.first,
00549 first->with_wild,
00550 first->item_list,
00551 first->where,
00552 first->order_list.elements + first->group_list.elements,
00553 (Order*) first->order_list.first,
00554 (Order*) first->group_list.first,
00555 first->having,
00556 first->options | session->options | SELECT_DESCRIBE,
00557 result,
00558 unit,
00559 first);
00560 }
00561 return (res || session->is_error());
00562 }
00563
00564 }