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

EXPLAIN in slow query log has wrong r_filtered for DELETE statement

    XMLWordPrintable

Details

    Description

      A slow query log entry for a DELETE statement showed the following:

      # 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;
      

      We can see that Rows_affected is 0. Supposedly, r_filtered is 55.55%.

      The documentation has the following definition of r_filtered:

      r_filtered is an observation-based counterpart of the filtered column. It shows which fraction of rows was left after applying the WHERE condition.

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

      If 55.55% of rows were left after applying the WHERE clause, then how were 0 rows affected by the delete? It seems likely that either r_filtered is completely wrong or the definition of the column is wrong.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            GeoffMontee Geoff Montee (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.