MySQL Bugs: #99107: Insufficient information in explain

Bug #99107	Insufficient information in explain
Submitted:	30 Mar 2020 12:35	Modified:	31 Mar 2020 20:29
Reporter:	Michael Bialik	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Explain does not show the original schema and table names for referenced tables ( aliases are used instead)

How to repeat:

I'm posting a simple explain:

explain format= json select * from sakila.actor a

{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "21.00"
},
"table": {
"table_name": "a",
"access_type": "ALL",
"rows_examined_per_scan": 200,
"rows_produced_per_join": 200,
"filtered": "100.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "20.00",
"prefix_cost": "21.00",
"data_read_per_join": "54K"
},
"used_columns": [
"actor_id",
"first_name",
"last_name",
"last_update"
]
}
}
}

As you can see there is no mentioning of schema name [sakila] and the original table name [actor] in the explain text (table alias is used instead.

Is it possible to display these values as part of explain?

Hello Bialik,

Thank you for your bug report.

Before proceeding any further, can you please provide us feedback on the options that are available within MySQL 8.0.

First of all, have you tried all EXPLAIN options. Have you tried EXPLAIN EXTENDED, then other output formats, like TREE or TRADITIONAL ???

Next, have you tried EXPLAIN ANALYZE, available in 8.0.19 ????

You can also look at the optimizer trace table within the information schema.

You can try all these options and you will find much more info. However, one thing is for sure. You should be able to get info on the table and its partition used, but schema is not available in all variants of the EXPLAIN.

Let us know if you have found a variant that suits your needs.

Hi Michael,

Thank you for the feature request!

We're trying not to expand the size of the EXPLAIN output too much. There's an almost infinite amount of information that we _could_ print. But if we print everything, it will be very hard to read the plan, so we have to make a careful selection. The purpose of EXPLAIN is to show the query plan, so we focus on doing that very well.

The fully qualified table name is an important piece of information, and we have considered adding it, but we rejected the idea because the information is already present in the query, so we would be expanding the output without adding extra information. We have noted your request, though, so if you have any good examples where this would make a huge difference, please add them to this feature request.

Sinisa: This is the same in all EXPLAIN formats.

Best regards,

Norvald

Thank you, Norvald.

Hello,  Norvald

Thank you for your explanation. However allow me to disagree with you. During my work I encountered a number of following cases when fully qualified table name [schema name + full table name] may help:
1. When you have a number of schemes in the same instance and you may have identical table names in a number of schemes 
2. Another case is when you are using predefined view and you may have an aliases inside the view that are identical to aliases you defined outside of the view

Eventually, it's possible to backtrack the definitions and to find the original tables, but it may take quite a long time.

Michael

Hi Mr. Bialik,

We agree with you, which is why this is a verified feature request.

This is not a verified bug, since the current functionality of EXPLAIN is exactly as it is designed.