Details
Critical
Description
Support EXPLAIN FORMAT=JSON like MySQL 5.6 does.
Difference from MySQL's EXPLAIN FORMAT=JSON
We don't want to copy MySQL 5.6:
From the user point of view:
- 5.6 output format is not documented and unstable (even MySQL Workbench fails to parse it in some cases)
- I don't expect that any 3rd party is able to parse it, other than relying that it's a well-formed JSON document.
- 5.6 output format doesn't allow to display some info we want to display
- 5.6's output format has legacy of tabular output format in many places
- MariaDB has more optimizer features, so we will have to produce output that 5.6 never produces
Currently supported
- Basic SELECTs
- Table access methods
- full table/index scans
- range access
- index_merge access
- I_S read optimizations
- "Using where" (attached_condition)
- rows and 'filtered' columns
- Index Condition Pushdown ('index_condition')
- UNIONs (incomplete)
- Item-based subqueries (incomplete)
- Basic join buffering
- Single-table UPDATE/DELETE
- Derived tables
- Non-merged semi-joins (JTBMs)
- [Merged] Semi-joins: SJ-Materialization
- [Merged] Semi-joins: FirstMatch, DuplicateElimination, LooseScan
Not yet supported (important)
- ORDER BY/GROUP BY/ DISTINCT handling – as agreed won't be ready before 10.1.2
Not yet supported (less important)
- Subquery cache feature
- Advanced join buffering (display MRR as a special kind of scan)
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-7248 EXPLAIN FORMAT=JSON: Better output
-
- Open
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-6995 EXPLAIN JSON and ORDER BY, GROUP BY, etc
-
- Open
-
- links to
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
How MySQL-5.6 displays semi-join subquery plans:
FirstMatch:
"table" : { .... ,"first_match" : "other_table", ... }
Duplicate Elimination:
"duplicates_removal": {
"using_temporary_table": true,
"nested_loop": [ ... tables are here ]
}
SJ-Materialization looks barely distinguishable from derived table:
"table": {
"table_name": "<subquery2>",
"access_type": "ALL",
"materialized_from_subquery": {
"using_temporary_table": true,
"query_block": {
... suquery goes here
}
}
LooseScan
The table being scanned has:
"loosescan": true,
hi guys, i started reading this mdev (thank you implementing this feature with mariadb!), i don't know if it's possible, but optimizer explain why it choise one index instead of another? check MDEV-7239 (an idea about how to report this) and MDEV-7125 (a problem where optimizer select the 'wrong index' (slower) instead the 'right index' (faster)), i don't know what is the optimizer "decision tree" (or something like it), but is it possible to report this "index choise" to dba/developer? it's very interesting to understand how to better use optimizer
i'm watching this mdev, if you need users to test, i'm here 
I think this should be resolved outside of this MDEV. Within the scope of this MDEV, we limit ourselves to printing basically EXPLAIN or ANALYZE-stmt output in JSON form, with more details. Will reply on MDEV-7239.
ok no problem i marked as "is blocked by mdev-6109" at mdev-7239
Pushed into 10.1 tree
MySQL Workbench parses output of EXPLAIN FORMAT=JSON, see plugins/wb.query.analysis/explain_renderer.py.
The code there makes some assumptions about the form of JSON structures. It is not immediately apparent what exactly the assumptions are.