← Back to branch summary

~eday/drizzle/eday-dev

« back to all changes in this revision

Viewing changes to drizzled/optimizer/explain_plan.cc

  • Committer: Eric Day
  • Date: 2010-01-07 20:02:38 UTC
  • mfrom: (971.3.291 staging)
  • Revision ID: eday@oddments.org-20100107200238-uqw8v6kv9pl7nny5
Merged trunk.

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
}