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

ANALYZE SELECT produces r_rows = NULL with UNIQUE index

    Details

      Description

      As Knowledge Base says:

      'r_rows' shows how many rows were actually read from the table.

      A NULL value means that table was not scanned even once.

      By performing a simple query (of a single result) on a single field with a non-unique index, a value of 1.0 is displayed:

      MariaDB [(none)]> create database db;
      Query OK, 1 row affected (0.002 sec)
       
      MariaDB [(none)]> use db;
      Database changed
      MariaDB [db]> create table t1 (id int auto_increment primary key, value varchar(10) not null)ENGINE=MyISAM;
      Query OK, 0 rows affected (0.034 sec)
       
      MariaDB [db]> insert into t1(value) values ('a'),('b'),('c'),('d'),('e');
      Query OK, 5 rows affected (0.001 sec)
      Records: 5  Duplicates: 0  Warnings: 0
       
      MariaDB [db]> create index value on t1(value);
      Query OK, 5 rows affected (0.064 sec)
      Records: 5  Duplicates: 0  Warnings: 0
       
      MariaDB [db]> analyze select * from t1 where value = 'c';
      +------+-------------+-------+------+---------------+-------+---------+-------+------+--------+----------+------------+-----------------------+
      | id   | select_type | table | type | possible_keys | key   | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra                 |
      +------+-------------+-------+------+---------------+-------+---------+-------+------+--------+----------+------------+-----------------------+
      |    1 | SIMPLE      | t1    | ref  | value         | value | 32      | const |    1 |   1.00 |   100.00 |     100.00 | Using index condition |
      +------+-------------+-------+------+---------------+-------+---------+-------+------+--------+----------+------------+-----------------------+
      1 row in set (0.001 sec)
      

      However, with a unique index, it produces NULL:

      MariaDB [db]> drop index value on t1;
      Query OK, 5 rows affected (0.067 sec)
      Records: 5  Duplicates: 0  Warnings: 0
       
      MariaDB [db]> create unique index value on t1(value);
      Query OK, 5 rows affected (0.044 sec)
      Records: 5  Duplicates: 0  Warnings: 0
       
      MariaDB [db]> analyze select * from t1 where value = 'c';
      +------+-------------+-------+-------+---------------+-------+---------+-------+------+--------+----------+------------+-------+
      | id   | select_type | table | type  | possible_keys | key   | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra |
      +------+-------------+-------+-------+---------------+-------+---------+-------+------+--------+----------+------------+-------+
      |    1 | SIMPLE      | t1    | const | value         | value | 32      | const |    1 |   NULL |   100.00 |       NULL |       |
      +------+-------------+-------+-------+---------------+-------+---------+-------+------+--------+----------+------------+-------+
      1 row in set (0.001 sec)
      

        Attachments

          Activity

            People

            • Assignee:
              alice Alice Sherepa
              Reporter:
              Barrendeitor Barrendeitor M.
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: