← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to drizzled/optimizer/explain_plan.cc

Remove PLUGIN and MODULES.

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