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

              Assignee:
              psergey Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: