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

EXPLAIN in slow query log has wrong r_filtered for DELETE statement

    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

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              GeoffMontee Geoff Montee
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: