Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6109

EXPLAIN JSON

Details

    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)

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-6995 EXPLAIN JSON and ORDER BY, GROUP BY, etc

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-7248 EXPLAIN FORMAT=JSON: Better output

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7264 Assertion `0' failed in subselect_engine::get_identifier() on EXPLAIN FORMAT=JSON

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7265 Assertion `0' failed in Explain_table_access::tag_to_json(Json_writer*, explain_extra_tag)

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7266 Assertion `!element_started' failed in Json_writer& Json_writer::add_member(const char*)

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7267 Server crashes in Item_field::print on ANALYZE FORMAT=JSON

          • Major - Major loss of function.
          • Closed
          links to

          Web Link MySQL Bug: "EXPLAIN FORMAT=json output not documented."

          Activity

            Ascending order - Click to sort in descending order
            View 10 older comments
            psergei Sergei Petrunia added a comment -

            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,

            psergei Sergei Petrunia added a comment - 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,
            rspadim roberto spadim added a comment -

            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

            rspadim roberto spadim added a comment - 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
            psergei Sergei Petrunia added a comment - - edited

            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.

            psergei Sergei Petrunia added a comment - - edited 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 .
            rspadim roberto spadim added a comment - - edited

            ok no problem i marked as "is blocked by mdev-6109" at mdev-7239

            rspadim roberto spadim added a comment - - edited ok no problem i marked as "is blocked by mdev-6109" at mdev-7239
            psergei Sergei Petrunia added a comment -

            Pushed into 10.1 tree

            psergei Sergei Petrunia added a comment - Pushed into 10.1 tree

            People

              psergei Sergei Petrunia
              serg Sergei Golubchik
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.