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

    XMLWordPrintable

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

            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.