← Back to team overview

maria-developers team mailing list archive

Re: MWL#182: a problem select_type column, SIMPLE vs PRIMARY


Hi, Sergey!

On Aug 27, Sergey Petrunya wrote:
> Hello,
> When coding MWL#182, I've found out that it is very difficult to print
> the same value of select_type as EXPLAIN does. 
> Problem description
> -------------------
> Moreover, any attempt to unify EXPLAIN and SHOW EXPLAIN code cause numerous
> test failures because of changed select_type.
> Current solution
> ----------------
> My approach to dealing with this is as follows:
> - MWL#182 code should not make changes to output of regular EXPLAIN SELECT
>   queries.
> - Outputs of SHOW EXPLAIN and EXPLAIN SELECT of the same query may be slightly
>   different

I think it's fine as an interim solution.

To have the problem recorded somewhere, please report a bug about
EXPLAIN reporting incorrect select_type.

> Future solution #1: get rid of SIMPLE
> -------------------------------------
> I don't see a value of having select_type=SIMPLE (other than term "simple"
> being encouraging for novice users:)  We could change SIMPLE to PRIMARY
> everywhere and get rid of the problem

Is this SIMPLE/PRIMARY the only difference between EXPLAIN SELECT and
SHOW EXPLAIN that you see?

> Future solution #2: use SIMPLE/PRIMARY approach of SHOW EXPLAIN
> ---------------------------------------------------------------
> EXPLAIN SELECT produces incorrect values because it calls 
> st_select_lex::set_explain_type() before the query rewrites are done, so 
> it can't see that
> - used VIEWs have subqueries
> - used subqueries will be flattened
> - etc.
> SHOW EXPLAIN operates on a query that is being executed, and that alone
> guarantees its output is closer to reality.

This makes me think that there may be other differences betwen them.
And that eventually explain select should be moved down the code path.
Which, I expect, will allow to remove quite a lot of duplicated code :)