← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to drizzled/sql_select.cc

  • Committer: Brian Aker
  • Date: 2009-12-18 23:28:10 UTC
  • mfrom: (1240.7.8 explain-plan)
  • Revision ID: brian@gaz-20091218232810-8u72it1739fprbfm
Merge from Padraig

Show diffs side-by-side

added added

removed removed

Lines of Context:
drizzled/sql_select.cc
65
65
using namespace std;
66
66
using namespace drizzled;
67
67
 
68
 
static const string access_method_str[]=
69
 
{
70
 
  "UNKNOWN",
71
 
  "system",
72
 
  "const",
73
 
  "eq_ref",
74
 
  "ref",
75
 
  "MAYBE_REF",
76
 
  "ALL",
77
 
  "range",
78
 
  "index",
79
 
  "ref_or_null",
80
 
  "unique_subquery",
81
 
  "index_subquery",
82
 
  "index_merge"
83
 
};
84
68
 
85
69
static int sort_keyuse(optimizer::KeyUse *a, optimizer::KeyUse *b);
86
70
static COND *build_equal_items(Session *session, COND *cond,
6552
6536
  return 0;
6553
6537
}
6554
6538
 
6555
 
/**
6556
 
  EXPLAIN handling.
6557
 
 
6558
 
  Send a description about what how the select will be done to stdout.
6559
 
*/
6560
 
void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
6561
 
                     bool distinct,const char *message)
6562
 
{
6563
 
  List<Item> field_list;
6564
 
  List<Item> item_list;
6565
 
  Session *session=join->session;
6566
 
  select_result *result=join->result;
6567
 
  Item *item_null= new Item_null();
6568
 
  const CHARSET_INFO * const cs= system_charset_info;
6569
 
  int quick_type;
6570
 
  /* Don't log this into the slow query log */
6571
 
  session->server_status&= ~(SERVER_QUERY_NO_INDEX_USED | SERVER_QUERY_NO_GOOD_INDEX_USED);
6572
 
  join->unit->offset_limit_cnt= 0;
6573
 
 
6574
 
  /*
6575
 
    NOTE: the number/types of items pushed into item_list must be in sync with
6576
 
    EXPLAIN column types as they're "defined" in Session::send_explain_fields()
6577
 
  */
6578
 
  if (message)
6579
 
  {
6580
 
    item_list.push_back(new Item_int((int32_t)
6581
 
                                     join->select_lex->select_number));
6582
 
    item_list.push_back(new Item_string(join->select_lex->type,
6583
 
                                        strlen(join->select_lex->type), cs));
6584
 
    for (uint32_t i=0 ; i < 7; i++)
6585
 
      item_list.push_back(item_null);
6586
 
    if (join->session->lex->describe & DESCRIBE_EXTENDED)
6587
 
      item_list.push_back(item_null);
6588
 
 
6589
 
    item_list.push_back(new Item_string(message,strlen(message),cs));
6590
 
    if (result->send_data(item_list))
6591
 
      join->error= 1;
6592
 
  }
6593
 
  else if (join->select_lex == join->unit->fake_select_lex)
6594
 
  {
6595
 
    /*
6596
 
      here we assume that the query will return at least two rows, so we
6597
 
      show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong
6598
 
      and no filesort will be actually done, but executing all selects in
6599
 
      the UNION to provide precise EXPLAIN information will hardly be
6600
 
      appreciated :)
6601
 
    */
6602
 
    char table_name_buffer[NAME_LEN];
6603
 
    item_list.empty();
6604
 
    /* id */
6605
 
    item_list.push_back(new Item_null);
6606
 
    /* select_type */
6607
 
    item_list.push_back(new Item_string(join->select_lex->type,
6608
 
                                        strlen(join->select_lex->type),
6609
 
                                        cs));
6610
 
    /* table */
6611
 
    {
6612
 
      Select_Lex *sl= join->unit->first_select();
6613
 
      uint32_t len= 6, lastop= 0;
6614
 
      memcpy(table_name_buffer, STRING_WITH_LEN("<union"));
6615
 
      for (; sl && len + lastop + 5 < NAME_LEN; sl= sl->next_select())
6616
 
      {
6617
 
        len+= lastop;
6618
 
        lastop= snprintf(table_name_buffer + len, NAME_LEN - len,
6619
 
                         "%u,", sl->select_number);
6620
 
      }
6621
 
      if (sl || len + lastop >= NAME_LEN)
6622
 
      {
6623
 
        memcpy(table_name_buffer + len, STRING_WITH_LEN("...>") + 1);
6624
 
        len+= 4;
6625
 
      }
6626
 
      else
6627
 
      {
6628
 
        len+= lastop;
6629
 
        table_name_buffer[len - 1]= '>';  // change ',' to '>'
6630
 
      }
6631
 
      item_list.push_back(new Item_string(table_name_buffer, len, cs));
6632
 
    }
6633
 
    /* type */
6634
 
    item_list.push_back(new Item_string(access_method_str[AM_ALL].c_str(),
6635
 
                                        access_method_str[AM_ALL].length(),
6636
 
                                        cs));
6637
 
    /* possible_keys */
6638
 
    item_list.push_back(item_null);
6639
 
    /* key*/
6640
 
    item_list.push_back(item_null);
6641
 
    /* key_len */
6642
 
    item_list.push_back(item_null);
6643
 
    /* ref */
6644
 
    item_list.push_back(item_null);
6645
 
    /* in_rows */
6646
 
    if (join->session->lex->describe & DESCRIBE_EXTENDED)
6647
 
      item_list.push_back(item_null);
6648
 
    /* rows */
6649
 
    item_list.push_back(item_null);
6650
 
    /* extra */
6651
 
    if (join->unit->global_parameters->order_list.first)
6652
 
      item_list.push_back(new Item_string("Using filesort",
6653
 
                                          14, cs));
6654
 
    else
6655
 
      item_list.push_back(new Item_string("", 0, cs));
6656
 
 
6657
 
    if (result->send_data(item_list))
6658
 
      join->error= 1;
6659
 
  }
6660
 
  else
6661
 
  {
6662
 
    table_map used_tables=0;
6663
 
    for (uint32_t i=0 ; i < join->tables ; i++)
6664
 
    {
6665
 
      JoinTable *tab=join->join_tab+i;
6666
 
      Table *table=tab->table;
6667
 
      char buff[512];
6668
 
      char buff1[512], buff2[512], buff3[512];
6669
 
      char keylen_str_buf[64];
6670
 
      String extra(buff, sizeof(buff),cs);
6671
 
      char table_name_buffer[NAME_LEN];
6672
 
      String tmp1(buff1,sizeof(buff1),cs);
6673
 
      String tmp2(buff2,sizeof(buff2),cs);
6674
 
      String tmp3(buff3,sizeof(buff3),cs);
6675
 
      extra.length(0);
6676
 
      tmp1.length(0);
6677
 
      tmp2.length(0);
6678
 
      tmp3.length(0);
6679
 
 
6680
 
      quick_type= -1;
6681
 
      item_list.empty();
6682
 
      /* id */
6683
 
      item_list.push_back(new Item_uint((uint32_t)
6684
 
                                       join->select_lex->select_number));
6685
 
      /* select_type */
6686
 
      item_list.push_back(new Item_string(join->select_lex->type,
6687
 
                                          strlen(join->select_lex->type),
6688
 
                                          cs));
6689
 
      if (tab->type == AM_ALL && tab->select && tab->select->quick)
6690
 
      {
6691
 
        quick_type= tab->select->quick->get_type();
6692
 
        if ((quick_type == optimizer::QuickSelectInterface::QS_TYPE_INDEX_MERGE) ||
6693
 
            (quick_type == optimizer::QuickSelectInterface::QS_TYPE_ROR_INTERSECT) ||
6694
 
            (quick_type == optimizer::QuickSelectInterface::QS_TYPE_ROR_UNION))
6695
 
          tab->type = AM_INDEX_MERGE;
6696
 
        else
6697
 
          tab->type = AM_RANGE;
6698
 
      }
6699
 
      /* table */
6700
 
      if (table->derived_select_number)
6701
 
      {
6702
 
        /* Derived table name generation */
6703
 
        int len= snprintf(table_name_buffer, sizeof(table_name_buffer)-1,
6704
 
                          "<derived%u>",
6705
 
                          table->derived_select_number);
6706
 
        item_list.push_back(new Item_string(table_name_buffer, len, cs));
6707
 
      }
6708
 
      else
6709
 
      {
6710
 
        TableList *real_table= table->pos_in_table_list;
6711
 
        item_list.push_back(new Item_string(real_table->alias,
6712
 
                                            strlen(real_table->alias),
6713
 
                                            cs));
6714
 
      }
6715
 
      /* "type" column */
6716
 
      item_list.push_back(new Item_string(access_method_str[tab->type].c_str(),
6717
 
                                          access_method_str[tab->type].length(),
6718
 
                                          cs));
6719
 
      /* Build "possible_keys" value and add it to item_list */
6720
 
      if (tab->keys.any())
6721
 
      {
6722
 
        uint32_t j;
6723
 
        for (j=0 ; j < table->s->keys ; j++)
6724
 
        {
6725
 
          if (tab->keys.test(j))
6726
 
          {
6727
 
            if (tmp1.length())
6728
 
              tmp1.append(',');
6729
 
            tmp1.append(table->key_info[j].name,
6730
 
                        strlen(table->key_info[j].name),
6731
 
                        system_charset_info);
6732
 
          }
6733
 
        }
6734
 
      }
6735
 
      if (tmp1.length())
6736
 
        item_list.push_back(new Item_string(tmp1.ptr(),tmp1.length(),cs));
6737
 
      else
6738
 
        item_list.push_back(item_null);
6739
 
 
6740
 
      /* Build "key", "key_len", and "ref" values and add them to item_list */
6741
 
      if (tab->ref.key_parts)
6742
 
      {
6743
 
        KEY *key_info=table->key_info+ tab->ref.key;
6744
 
        register uint32_t length;
6745
 
        item_list.push_back(new Item_string(key_info->name,
6746
 
                                            strlen(key_info->name),
6747
 
                                            system_charset_info));
6748
 
        length= int64_t2str(tab->ref.key_length, keylen_str_buf, 10) -
6749
 
                keylen_str_buf;
6750
 
        item_list.push_back(new Item_string(keylen_str_buf, length,
6751
 
                                            system_charset_info));
6752
 
        for (StoredKey **ref=tab->ref.key_copy ; *ref ; ref++)
6753
 
        {
6754
 
          if (tmp2.length())
6755
 
            tmp2.append(',');
6756
 
          tmp2.append((*ref)->name(), strlen((*ref)->name()),
6757
 
                      system_charset_info);
6758
 
        }
6759
 
        item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs));
6760
 
      }
6761
 
      else if (tab->type == AM_NEXT)
6762
 
      {
6763
 
        KEY *key_info=table->key_info+ tab->index;
6764
 
        register uint32_t length;
6765
 
        item_list.push_back(new Item_string(key_info->name,
6766
 
                                            strlen(key_info->name),cs));
6767
 
        length= int64_t2str(key_info->key_length, keylen_str_buf, 10) -
6768
 
                keylen_str_buf;
6769
 
        item_list.push_back(new Item_string(keylen_str_buf,
6770
 
                                            length,
6771
 
                                            system_charset_info));
6772
 
        item_list.push_back(item_null);
6773
 
      }
6774
 
      else if (tab->select && tab->select->quick)
6775
 
      {
6776
 
        tab->select->quick->add_keys_and_lengths(&tmp2, &tmp3);
6777
 
        item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs));
6778
 
        item_list.push_back(new Item_string(tmp3.ptr(),tmp3.length(),cs));
6779
 
        item_list.push_back(item_null);
6780
 
      }
6781
 
      else
6782
 
      {
6783
 
        item_list.push_back(item_null);
6784
 
        item_list.push_back(item_null);
6785
 
        item_list.push_back(item_null);
6786
 
      }
6787
 
 
6788
 
      /* Add "rows" field to item_list. */
6789
 
      double examined_rows;
6790
 
      if (tab->select && tab->select->quick)
6791
 
        examined_rows= rows2double(tab->select->quick->records);
6792
 
      else if (tab->type == AM_NEXT || tab->type == AM_ALL)
6793
 
        examined_rows= rows2double(tab->limit ? tab->limit :
6794
 
            tab->table->cursor->records());
6795
 
      else
6796
 
      {
6797
 
        optimizer::Position cur_pos= join->getPosFromOptimalPlan(i);
6798
 
        examined_rows= cur_pos.getFanout();
6799
 
      }
6800
 
 
6801
 
      item_list.push_back(new Item_int((int64_t) (uint64_t) examined_rows,
6802
 
            MY_INT64_NUM_DECIMAL_DIGITS));
6803
 
 
6804
 
      /* Add "filtered" field to item_list. */
6805
 
      if (join->session->lex->describe & DESCRIBE_EXTENDED)
6806
 
      {
6807
 
        float f= 0.0;
6808
 
        if (examined_rows)
6809
 
        {
6810
 
          optimizer::Position cur_pos= join->getPosFromOptimalPlan(i);
6811
 
          f= (float) (100.0 * cur_pos.getFanout() /
6812
 
              examined_rows);
6813
 
        }
6814
 
        item_list.push_back(new Item_float(f, 2));
6815
 
      }
6816
 
 
6817
 
      /* Build "Extra" field and add it to item_list. */
6818
 
      bool key_read=table->key_read;
6819
 
      if ((tab->type == AM_NEXT || tab->type == AM_CONST) &&
6820
 
          table->covering_keys.test(tab->index))
6821
 
        key_read=1;
6822
 
      if (quick_type == optimizer::QuickSelectInterface::QS_TYPE_ROR_INTERSECT &&
6823
 
          ! ((optimizer::QuickRorIntersectSelect *) tab->select->quick)->need_to_fetch_row)
6824
 
        key_read=1;
6825
 
 
6826
 
      if (tab->info)
6827
 
        item_list.push_back(new Item_string(tab->info,strlen(tab->info),cs));
6828
 
      else if (tab->packed_info & TAB_INFO_HAVE_VALUE)
6829
 
      {
6830
 
        if (tab->packed_info & TAB_INFO_USING_INDEX)
6831
 
          extra.append(STRING_WITH_LEN("; Using index"));
6832
 
        if (tab->packed_info & TAB_INFO_USING_WHERE)
6833
 
          extra.append(STRING_WITH_LEN("; Using where"));
6834
 
        if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
6835
 
          extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
6836
 
        /* Skip initial "; "*/
6837
 
        const char *str= extra.ptr();
6838
 
        uint32_t len= extra.length();
6839
 
        if (len)
6840
 
        {
6841
 
          str += 2;
6842
 
          len -= 2;
6843
 
        }
6844
 
        item_list.push_back(new Item_string(str, len, cs));
6845
 
      }
6846
 
      else
6847
 
      {
6848
 
        uint32_t keyno= MAX_KEY;
6849
 
        if (tab->ref.key_parts)
6850
 
          keyno= tab->ref.key;
6851
 
        else if (tab->select && tab->select->quick)
6852
 
          keyno = tab->select->quick->index;
6853
 
 
6854
 
        if (quick_type == optimizer::QuickSelectInterface::QS_TYPE_ROR_UNION ||
6855
 
            quick_type == optimizer::QuickSelectInterface::QS_TYPE_ROR_INTERSECT ||
6856
 
            quick_type == optimizer::QuickSelectInterface::QS_TYPE_INDEX_MERGE)
6857
 
        {
6858
 
          extra.append(STRING_WITH_LEN("; Using "));
6859
 
          tab->select->quick->add_info_string(&extra);
6860
 
        }
6861
 
          if (tab->select)
6862
 
        {
6863
 
          if (tab->use_quick == 2)
6864
 
          {
6865
 
            /*
6866
 
             * To print out the bitset in tab->keys, we go through
6867
 
             * it 32 bits at a time. We need to do this to ensure
6868
 
             * that the to_ulong() method will not throw an
6869
 
             * out_of_range exception at runtime which would happen
6870
 
             * if the bitset we were working with was larger than 64
6871
 
             * bits on a 64-bit platform (for example).
6872
 
             */
6873
 
            stringstream s, w;
6874
 
            string str;
6875
 
            w << tab->keys;
6876
 
            w >> str;
6877
 
            for (uint32_t pos= 0; pos < tab->keys.size(); pos+= 32)
6878
 
            {
6879
 
              bitset<32> tmp(str, pos, 32);
6880
 
              if (tmp.any())
6881
 
                s << uppercase << hex << tmp.to_ulong();
6882
 
            }
6883
 
            extra.append(STRING_WITH_LEN("; Range checked for each "
6884
 
                                         "record (index map: 0x"));
6885
 
            extra.append(s.str().c_str());
6886
 
            extra.append(')');
6887
 
          }
6888
 
          else if (tab->select->cond)
6889
 
          {
6890
 
            extra.append(STRING_WITH_LEN("; Using where"));
6891
 
          }
6892
 
        }
6893
 
        if (key_read)
6894
 
        {
6895
 
          if (quick_type == optimizer::QuickSelectInterface::QS_TYPE_GROUP_MIN_MAX)
6896
 
            extra.append(STRING_WITH_LEN("; Using index for group-by"));
6897
 
          else
6898
 
            extra.append(STRING_WITH_LEN("; Using index"));
6899
 
        }
6900
 
        if (table->reginfo.not_exists_optimize)
6901
 
          extra.append(STRING_WITH_LEN("; Not exists"));
6902
 
 
6903
 
        if (need_tmp_table)
6904
 
        {
6905
 
          need_tmp_table=0;
6906
 
          extra.append(STRING_WITH_LEN("; Using temporary"));
6907
 
        }
6908
 
        if (need_order)
6909
 
        {
6910
 
          need_order=0;
6911
 
          extra.append(STRING_WITH_LEN("; Using filesort"));
6912
 
        }
6913
 
        if (distinct & test_all_bits(used_tables,session->used_tables))
6914
 
          extra.append(STRING_WITH_LEN("; Distinct"));
6915
 
 
6916
 
        if (tab->insideout_match_tab)
6917
 
        {
6918
 
          extra.append(STRING_WITH_LEN("; LooseScan"));
6919
 
        }
6920
 
 
6921
 
        for (uint32_t part= 0; part < tab->ref.key_parts; part++)
6922
 
        {
6923
 
          if (tab->ref.cond_guards[part])
6924
 
          {
6925
 
            extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
6926
 
            break;
6927
 
          }
6928
 
        }
6929
 
 
6930
 
        if (i > 0 && tab[-1].next_select == sub_select_cache)
6931
 
          extra.append(STRING_WITH_LEN("; Using join buffer"));
6932
 
 
6933
 
        /* Skip initial "; "*/
6934
 
        const char *str= extra.ptr();
6935
 
        uint32_t len= extra.length();
6936
 
        if (len)
6937
 
        {
6938
 
          str += 2;
6939
 
          len -= 2;
6940
 
        }
6941
 
        item_list.push_back(new Item_string(str, len, cs));
6942
 
      }
6943
 
      // For next iteration
6944
 
      used_tables|=table->map;
6945
 
      if (result->send_data(item_list))
6946
 
        join->error= 1;
6947
 
    }
6948
 
  }
6949
 
  for (Select_Lex_Unit *unit= join->select_lex->first_inner_unit();
6950
 
       unit;
6951
 
       unit= unit->next_unit())
6952
 
  {
6953
 
    if (mysql_explain_union(session, unit, result))
6954
 
      return;
6955
 
  }
6956
 
  return;
6957
 
}
6958
 
 
6959
 
bool mysql_explain_union(Session *session, Select_Lex_Unit *unit, select_result *result)
6960
 
{
6961
 
  bool res= false;
6962
 
  Select_Lex *first= unit->first_select();
6963
 
 
6964
 
  for (Select_Lex *sl= first;
6965
 
       sl;
6966
 
       sl= sl->next_select())
6967
 
  {
6968
 
    // drop UNCACHEABLE_EXPLAIN, because it is for internal usage only
6969
 
    uint8_t uncacheable= (sl->uncacheable & ~UNCACHEABLE_EXPLAIN);
6970
 
    sl->type= (((&session->lex->select_lex)==sl)?
6971
 
               (sl->first_inner_unit() || sl->next_select() ?
6972
 
                "PRIMARY" : "SIMPLE"):
6973
 
               ((sl == first)?
6974
 
                ((sl->linkage == DERIVED_TABLE_TYPE) ?
6975
 
                 "DERIVED":
6976
 
                 ((uncacheable & UNCACHEABLE_DEPENDENT) ?
6977
 
                  "DEPENDENT SUBQUERY":
6978
 
                  (uncacheable?"UNCACHEABLE SUBQUERY":
6979
 
                   "SUBQUERY"))):
6980
 
                ((uncacheable & UNCACHEABLE_DEPENDENT) ?
6981
 
                 "DEPENDENT UNION":
6982
 
                 uncacheable?"UNCACHEABLE UNION":
6983
 
                 "UNION")));
6984
 
    sl->options|= SELECT_DESCRIBE;
6985
 
  }
6986
 
  if (unit->is_union())
6987
 
  {
6988
 
    unit->fake_select_lex->select_number= UINT_MAX; // jost for initialization
6989
 
    unit->fake_select_lex->type= "UNION RESULT";
6990
 
    unit->fake_select_lex->options|= SELECT_DESCRIBE;
6991
 
    if (!(res= unit->prepare(session, result, SELECT_NO_UNLOCK | SELECT_DESCRIBE)))
6992
 
      res= unit->exec();
6993
 
    res|= unit->cleanup();
6994
 
  }
6995
 
  else
6996
 
  {
6997
 
    session->lex->current_select= first;
6998
 
    unit->set_limit(unit->global_parameters);
6999
 
    res= mysql_select(session, &first->ref_pointer_array,
7000
 
                        (TableList*) first->table_list.first,
7001
 
                        first->with_wild, first->item_list,
7002
 
                        first->where,
7003
 
                        first->order_list.elements +
7004
 
                        first->group_list.elements,
7005
 
                        (order_st*) first->order_list.first,
7006
 
                        (order_st*) first->group_list.first,
7007
 
                        first->having,
7008
 
                        first->options | session->options | SELECT_DESCRIBE,
7009
 
                        result, unit, first);
7010
 
  }
7011
 
  return(res || session->is_error());
7012
 
}
7013
6539
 
7014
6540
static void print_table_array(Session *session, String *str, TableList **table,
7015
6541
                              TableList **end)