WL#4168: Implement EXPLAIN ANALYZE

Affects: WorkLog-3.4 — Status: Un-Assigned — Priority: Medium

This task is to implement EXPLAIN [EXTENDED] ANALYZE. The command should work in
a similar way to PostgreSQL's EXPLAIN ANALYZE: it should run the query and then
produce EXPLAIN output with additional information about how optimizer's
expectation matched the actual execution.

Comments by Kolbe Kegel:

This may be a good opportunity to provide some facility whereby a storage engine
could tell the user, in detail, what rows it would need to lock and any other
engine-internal information about how the query is executed.


A sketch of what could be done:



EXPLAIN output has "rows" column which shows how many records MySQL expects to

scan in each table. EXPLAIN ANALYZE should produce additional column named

"actual_rows" (other name suggestions welcome) which will show how many records

MySQL actually had to scan.



EXPLAIN EXTENDED has "filtered" column which shows the fraction of record

combinations that will be filtered out by the part of the WHERE clause attached

to each table. EXPLAIN EXTENDED ANALYZE should have "actual_filtered" column

which will show the  fraction of record combination that was actually filtered out.

You must be logged in to tag this worklog

On Aug 10, 2009 @ 10:53, Markweee said:

The Times Square area consists of the blocks between Sixth and Eighth Avenues from east to west, and West 40th and West 53rd Streets from south to north, making up the western part of the commercial area of Midtown Manhattan. biology degree AND online business school

On Jul 25, 2009 @ 19:20, alquma said:

> Hi, > > It doesn't really make sense, but a crash is a crash ;) > > How-to-repeat : > > mysql> EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(); > ERROR 2013: Lost connection to MySQL server during query > > Stack trace : > > 0x80a3911 handle_segfault + 481 > 0x8290498 pthread_sighandler + 176 > 0x8098f48 select_send::send_data(List&) + 40 > 0x80d7125 select_describe(JOIN*, bool, bool, bool, char const*) + 2949 > 0x80ce863 JOIN::exec() + 675 > 0x80cf87b mysql_select(THD*, st_table_list*, List&, Item*, st_order*, > st_order*, Item*, st_order*, unsigned long, select_result*, > st_select_lex_unit*, st_select_lex*) + 123 > 0x80d21a4 mysql_explain_union(THD*, st_select_lex_unit*, select_result*) + > 196 > 0x80b0a9a mysql_execute_command(THD*) + 858 > 0x80b57d8 mysql_parse(THD*, char*, unsigned) + 248 > 0x80af64a dispatch_command(enum_server_command, THD*, char*, unsigned) + 842 > 0x80af2e6 do_command(THD*) + 118 > 0x80aebce handle_one_connection + 910 > 0x828deba pthread_start_thread + 218 > 0x82c21da thread_start + 4 > > Regards, > Bolsa de Trabajo

On Jun 4, 2009 @ 18:51, makemine said:

Need to know more of this project for our Forex Money

On Apr 7, 2009 @ 03:46, Sandrawillson said:

Pay Per Click ManagementSometimes the idea that all of the topics have been previously discussed is quite discouraging. But when you take a look at it, that is part of life, it just evolved to something very different yet its still the same.

Thanks for sharing this!

Votes

  • Rated 5.00 out of 5
Rated 5.00 out of 5 with 4 votes cast.
You must be logged in to vote.

Watches

5 members are watching this worklog
You must be logged in to track this worklog.

Provide Feedback

Please note:
HTML will be purified, but we allow for a number of HTML tags so that you have the flexibility to decorate your comment text to some extent. The comments allow the following HTML tags:

strong, b, em, blockquote, a, code, pre

To put code into your comment, simply encapsulate your code with
[code language="XXX"][/code], where XXX is any common language, for instance "PHP", "SQL", "C", etc.

You must be logged in to comment