Bug #12568 Add a show QEP funktion as a SQL command
Submitted: 13 Aug 2005 10:44 Modified: 23 Sep 2005 21:16
Reporter: Peter Volk (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: qc
Triage: D5 (Feature request)

[13 Aug 2005 10:44] Peter Volk
Description:
I would like to be able to view the QEP of a query. I need this to be able to do perfomance enhancements to the database truckture and queries. Adding a SQL Funktion like:

SHOW QEP FOR SELECT... FROM... WHERE....

How to repeat:
none
[9 Sep 2005 9:22] Marko Mäkelä
Do you mean Query Evaluation Plan? How should it differ from EXPLAIN SELECT?
[9 Sep 2005 11:13] Peter Volk
Well yes. I know it as Query execution plan. 

It should differ from explain from exactly showing the evaluation. Currently the explain thingy only tells you an estimate. of scaned rows etc. the show qep function should show the exact plan how the query is evaluated. e.g when does it so a selection when a projection when does it use a certain inex etc. 

Right now you can only see what keys the system would use but not what elections are paralell etc.

Does that make any sense?
[9 Sep 2005 12:05] Marko Mäkelä
Yes, it does make sense. Now that MySQL supports subqueries and views, a query execution plan output would be a very useful diagnostics aid.

To my knowledge, MySQL never executes independent components of a query in parallel. (InnoDB is capable of that, but query execution in MySQL takes place above the storage engine layer.) As the execution is strictly sequential, it should be possible to guess the query execution plan from the EXPLAIN SELECT output, at least for simple queries.
[9 Sep 2005 12:14] Peter Volk
So if I understood correctly MySql executes one single query completely sequential? Strange. Wouldn't it be a performance increase if e.g. subselects were executed parallel? Is there any documentation on why it only is executed sequential

How about extending the explain function to show more details e.g. showing the rewritten query for views (or the explains for the queries in the view) or some more details for Stroed procedures?
[9 Sep 2005 12:31] Marko Mäkelä
See also EXPLAIN EXTENDED.

There are plans for having a 'better EXPLAIN' known as 'SMART EXPLAIN', but I am afraid it will be something for the release after 5.1.
[9 Sep 2005 12:39] Peter Volk
Hmm good to know. I can't find the EXPLAIN EXTENDED in the docs. Is it new in 5.0 tree? What would the SMART EXPLAIN have for features? anyway I could help to push it a bit? I can do some coding if necessary.
[9 Sep 2005 13:22] Marko Mäkelä
It looks like EXPLAIN EXTENDED SELECT ... is already in MySQL 4.1. I will file a documentation bug report about that.

I am not familiar with the implementation details of the query evaluator, and thus I cannot estimate how much work this would be. All resources are now directed to making MySQL 5.0 stable.
[9 Sep 2005 13:31] Peter Volk
Can you name me someone to talk with about the complextity of the code? I know that everyone is working on the 5.0 tree (I'm also wating for the final release). Thats why i'm offering help.

Thanks for reporting the documentation Bug.
[12 Sep 2005 7:28] Marko Mäkelä
Sergei Golubchik says that the feature freeze for MySQL 5.1 was in August and that the task is a very complex one. Furthermore, the 'smart explain' in the planned form is not the query execution plan you are looking for.

If the QEP is very important to you, maybe you should contact the MySQL sales people and ask for possible options.
[23 Sep 2005 21:16] Peter Volk
Hey,

Is there a document that I can read that lists the features frozen for the 5.1? Also what are the plans to display in the smart explain query? 

I've been doing a bit of research on the source code of MySql. The suggested Show QEP function would be doing logging in the JOIN::exec function. Most of this logging is already covered by several DEBUG stuff. Also one would need to look into the index classes. Also the syntax stuff would need to be modified. Now since this still is quite amount of work is there a way to approve this project by MySql? Also certain details would need to be defined for the format etc....

Thanks Peter