Bug #71973 Visual explain fails on large explain plan
Submitted: 7 Mar 2014 13:28 Modified: 14 May 2014 23:41
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:6.1.2.11661 OS:Any
Assigned to: CPU Architecture:Any
Tags: explain

[7 Mar 2014 13:28] Daniël van Eeden
Description:
A query for which a normal explain returns 77 rows fails in workbench.

How to repeat:
Click explain a complex query.
[7 Mar 2014 13:28] Daniël van Eeden
screenshot of the error

Attachment: wb_explain.png (image/png, text), 17.34 KiB.

[7 Mar 2014 14:14] Miguel Solorzano
Please try version 6.0.9 or 6.1.2. Thanks.
[7 Mar 2014 14:27] Daniël van Eeden
With 6.1.2 it does work, but it is really really slow on a core i7 16GB Win7 laptop.

The icons on the image are really tiny and not very readble.

the raw explain output is 1830 lines and contains many materialized_from_subqueries and nested loop joins.

The exported image is a 8650 x 3908 png image.
[7 Mar 2014 17:29] Alfredo Kojima
Hi Daniel
Could you attach the json output of your query so we can use it as a test case?
[7 Mar 2014 17:30] Alfredo Kojima
I mean the JSON output of the EXPLAIN for your query, you can copy/paste it from the Raw Output tab in the explain panel.
[7 Mar 2014 17:32] Daniël van Eeden
Unfortunately that's not possible. The json contains lots of proprietary information.
[7 Mar 2014 17:49] Alfredo Kojima
What if you do a find/replace to obscure all identifiers and names?
[8 Mar 2014 20:18] Daniël van Eeden
I've tried to create a somewhat similar explain.

The image smaller and on a Ubuntu Linux system it doesn't feel slow.

However:
- Zooming (and/or an overview window) would be helpful.
- I would like to open the image in a new tab or fullscreen so I can have more of the image visible.
- The canvas size seems to small (Bug #71980)
- Two boxes are drawn on top of each other.
[8 Mar 2014 20:19] Daniël van Eeden
exported image

Attachment: foo.png (image/png, text), 113.93 KiB.

[8 Mar 2014 20:19] Daniël van Eeden
Example query

Attachment: foo.sql (text/x-sql), 2.73 KiB.

[9 Mar 2014 14:34] Daniël van Eeden
Generated query which fails with 6.1.2 visual explain

Attachment: q31.sql (text/x-sql), 2.84 KiB.

[9 Mar 2014 14:36] Daniël van Eeden
Error message for q32.sql visual explain

Attachment: Screenshot from 2014-03-09 15:35:17.png (image/png, text), 14.17 KiB.

[9 Mar 2014 14:37] Daniël van Eeden
Query generation script (./qgen.py <num>)

Attachment: qgen.py (text/x-python), 371 bytes.

[9 Mar 2014 14:58] Daniël van Eeden
Another generated query with many joins and subqueries

Attachment: manytable.sql (text/x-sql), 8.12 KiB.

[10 Mar 2014 8:56] Umesh Shastry
Hello Daniel,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[13 Mar 2014 11:16] Daniël van Eeden
add explain tag
[14 May 2014 23:41] Philip Olson
Fixed as of the upcoming MySQL Workbench 6.1.6 release, and here's the changelog entry:

Visual Explain could fail to function for large "EXPLAIN" plans.

Thank you for yet another detailed bug report.