← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to drizzled/optimizer/explain_plan.cc

  • Committer: Jay Pipes
  • Date: 2008-07-17 17:54:00 UTC
  • mto: This revision was merged to the branch mainline in revision 182.
  • Revision ID: jay@mysql.com-20080717175400-xm2aazihjra8mdzq
Removal of DBUG from libdrizzle/ - Round 2

Show diffs side-by-side

added added

removed removed

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