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

Update documentation on "EXPLAIN" and "EXPLAIN in the Slow Query Log"

    XMLWordPrintable

Details

    Description

      In MariaDB 10.1.0 and later, the r_rows and r_filtered columns were added to the EXPLAIN output that appears in the slow query log. For example:

      # User@Host: user[user] @ test.domain.com [10.6.184.141]
      # Thread_id: 75816 Schema: db QC_hit: No
      # Query_time: 420.906319 Lock_time: 0.086885 Rows_sent: 0 Rows_examined: 9734191
      # Rows_affected: 0
      #
      # explain: id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
      # explain: 1 SIMPLE tab ALL a_timestamp_indx NULL NULL NULL 9330481 9734191.00 100.00 55.55 Using where
      #
      SET timestamp=1535157447;
      delete from tab where a_timestamp < (unix_timestamp(now())-14400)*1000000;
      

      Updates to "EXPLAIN in the Slow Query Log"

      The documentation page for "EXPLAIN in the Slow Query Log" will need some updates:

      https://mariadb.com/kb/en/library/explain-in-the-slow-query-log/

      The page should mention that these two new columns were added in MariaDB 10.1.

      The examples on the page should contain the two new columns.

      The page should mention that these two new columns are not printed with normal EXPLAIN output. They are printed with ANALYZE output. For example, something like this:

      In MariaDB 10.1.0 and later, the EXPLAIN output in the slow query log also includes two new columns that a user wouldn't see if they manually executed the EXPLAIN statement. These two new columns are r_rows and r_filtered. If a user wanted to see these two new columns outside of the slow query log, then they would need to execute the ANALYZE statement instead.

      And it should link to here:

      https://mariadb.com/kb/en/library/analyze-statement/

      Updates to "EXPLAIN"

      The documentation page for "EXPLAIN" will need some updates:

      https://mariadb.com/kb/en/library/explain/

      The page currently says this:

      ANALYZE statement, which performs the query as well as producing EXPLAIN output, and provides actual as well as estimated statistics, has been available from MariaDB 10.1.0.

      This is not very clear. It should probably say something like this:

      In MariaDB 10.1.0 and later, MariaDB also supports the ANALYZE statement. The ANALYZE statement is similar to the EXPLAIN statement, but it actually executes the query. The ANALYZE satement adds the r_rows and r_filtered columns to the output to show how the real values differ from the estimated values.

      https://mariadb.com/kb/en/library/analyze-statement/

      The existing examples on this page don't need to be updated, because the two new columns were not added to the output of the regular EXPLAIN statement.

      Attachments

        Issue Links

          Activity

            People

              dbart Daniel Bartholomew
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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