Drizzled Public API Documentation

explain_plan.cc

00001 /* -*- mode: c++; c-basic-offset: 2; indent-tabs-mode: nil; -*-
00002  *  vim:expandtab:shiftwidth=2:tabstop=2:smarttab:
00003  *
00004  *  Copyright (C) 2008-2009 Sun Microsystems, Inc.
00005  *
00006  *  This program is free software; you can redistribute it and/or modify
00007  *  it under the terms of the GNU General Public License as published by
00008  *  the Free Software Foundation; version 2 of the License.
00009  *
00010  *  This program is distributed in the hope that it will be useful,
00011  *  but WITHOUT ANY WARRANTY; without even the implied warranty of
00012  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
00013  *  GNU General Public License for more details.
00014  *
00015  *  You should have received a copy of the GNU General Public License
00016  *  along with this program; if not, write to the Free Software
00017  *  Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
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   /* Don't log this into the slow query log */
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    NOTE: the number/types of items pushed into item_list must be in sync with
00091    EXPLAIN column types as they're "defined" in Session::send_explain_fields()
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        here we assume that the query will return at least two rows, so we
00114        show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong
00115        and no filesort will be actually done, but executing all selects in
00116        the UNION to provide precise EXPLAIN information will hardly be
00117        appreciated :)
00118      */
00119     char table_name_buffer[NAME_LEN];
00120     item_list.clear();
00121     /* id */
00122     item_list.push_back(new Item_null);
00123     /* select_type */
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     /* table */
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]= '>';  // change ',' to '>'
00147       }
00148       item_list.push_back(new Item_string(table_name_buffer, len, cs));
00149     }
00150     /* type */
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     /* possible_keys */
00155     item_list.push_back(item_null);
00156     /* key*/
00157     item_list.push_back(item_null);
00158     /* key_len */
00159     item_list.push_back(item_null);
00160     /* ref */
00161     item_list.push_back(item_null);
00162     /* in_rows */
00163     if (join->session->lex().describe & DESCRIBE_EXTENDED)
00164       item_list.push_back(item_null);
00165     /* rows */
00166     item_list.push_back(item_null);
00167     /* extra */
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       /* id */
00195       item_list.push_back(new Item_uint((uint32_t)
00196             join->select_lex->select_number));
00197       /* select_type */
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       /* table */
00212       if (table->derived_select_number)
00213       {
00214         /* Derived table name generation */
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       /* "type" column */
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       /* Build "possible_keys" value and add it to item_list */
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       /* Build "key", "key_len", and "ref" values and add them to item_list */
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       /* Add "rows" field to item_list. */
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       /* Add "filtered" field to item_list. */
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       /* Build "Extra" field and add it to item_list. */
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);        /* Skip initial "; "*/
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              * To print out the bitset in tab->keys, we go through
00373              * it 32 bits at a time. We need to do this to ensure
00374              * that the to_ulong() method will not throw an
00375              * out_of_range exception at runtime which would happen
00376              * if the bitset we were working with was larger than 64
00377              * bits on a 64-bit platform (for example).
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       // For next iteration
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     // drop UNCACHEABLE_EXPLAIN, because it is for internal usage only
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; // just for initialization
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 } /* namespace drizzled */