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