Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
10.1.38, 10.3.14, 10.4.4
-
Windows, Debian Linux
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) |