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

ANALYZE SELECT produces strange r_filtered value on a query with ORDER BY

Details

    Description

      MariaDB [test]> create table t1 (a int, b int) engine=MyISAM;
      Query OK, 0 rows affected (0.24 sec)
       
      MariaDB [test]> insert into t1 values (1,100),(2,200),(3,300),(4,400);
      Query OK, 4 rows affected (0.00 sec)
      Records: 4  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> analyze select * from t1 where b > 300;
      +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 |      4 |   100.00 |      25.00 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> analyze select * from t1 where b > 300 order by b;
      +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-----------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra                       |
      +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-----------------------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 |      1 |   100.00 |     100.00 | Using where; Using filesort |
      +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-----------------------------+
      1 row in set (0.00 sec)

      Same with a bigger number of rows:

      MariaDB [test]> insert into t1 select * from t1;
      Query OK, 4 rows affected (0.00 sec)
      Records: 4  Duplicates: 0  Warnings: 0
      ...
      ...
       
      MariaDB [test]> analyze select * from t1 where b > 300;
      +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 4096 |   4096 |   100.00 |      25.00 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
      1 row in set (0.01 sec)
       
      MariaDB [test]> analyze select * from t1 where b > 300 order by b;
      +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-----------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra                       |
      +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-----------------------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 4096 |   1024 |   100.00 |     100.00 | Using where; Using filesort |
      +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-----------------------------+
      1 row in set (0.00 sec)

      Maybe it's by design, but then it needs to be documented, now KB just says

      It shows which fraction of rows was left after applying the WHERE condition.

      https://mariadb.com/kb/en/mariadb/documentation/sql-commands/administration-commands/analyze-statement/

      Attachments

        Issue Links

          Activity

            ANALYZE FORMAT=JSON produces this:

            MariaDB [j1a]> analyze format=json select * from t1 where b > 300 order by b\G
            *************************** 1. row ***************************
            ANALYZE: {
              "query_block": {
                "select_id": 1,
                "r_loops": 1,
                "r_total_time_ms": 0.1379,
                "read_sorted_file": {
                  "r_rows": 1,
                  "filesort": {
                    "r_loops": 1,
                    "r_used_priority_queue": false,
                    "r_output_rows": 1,
                    "r_buffer_size": "308",
                    "table": {
                      "table_name": "t1",
                      "access_type": "ALL",
                      "r_loops": 1,
                      "rows": 4,
                      "r_rows": 4,
                      "r_total_time_ms": 0.0073,
                      "filtered": 100,
                      "r_filtered": 0.25,
                      "attached_condition": "(t1.b > 300)"
                    }
                  }
                }
              }
            }

            Note that r_filtered near attached_condition shows the right value.

            psergei Sergei Petrunia added a comment - ANALYZE FORMAT=JSON produces this: MariaDB [j1a]> analyze format=json select * from t1 where b > 300 order by b\G *************************** 1. row *************************** ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 0.1379, "read_sorted_file": { "r_rows": 1, "filesort": { "r_loops": 1, "r_used_priority_queue": false, "r_output_rows": 1, "r_buffer_size": "308", "table": { "table_name": "t1", "access_type": "ALL", "r_loops": 1, "rows": 4, "r_rows": 4, "r_total_time_ms": 0.0073, "filtered": 100, "r_filtered": 0.25, "attached_condition": "(t1.b > 300)" } } } } } Note that r_filtered near attached_condition shows the right value.

            We could still fix the tabular output, too.

            psergei Sergei Petrunia added a comment - We could still fix the tabular output, too.

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.