← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to drizzled/optimizer/explain_plan.cc

Merged in changes. 
Edited a the comment test case so deal with our version bump.

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